cDataSet.min and max - Usage example?

[EGMono]

cDataSet includes the functions min and max, but I can't seem to find
any usage examples (except the one in the heat map example) - my
question is how do I, if possible, get the min and max of a single
cDataColumn?

Would I be better off writing a min and max (as well as sum and
average) functions for the cDataColumn based on the min and max
functions for cDataSet?

Thanks in advance!

[Bruce Mcpherson]

Hello

I didn't implement a min and max for columns or rows - but I guess I should have. 

In any case, it's pretty trivial.. just add these to the cdatacolumn class - i havent tested this. Let me know if it works and i will probably incorporate into next release along with a cDataRow version too i guess..

Public Function max() As cCell

    Dim cc As cCell, dc As cCell

    For Each dc In Rows

        If cc Is Nothing Then

            Set cc = dc

        ElseIf dc.Value > cc.Value Then

            Set cc = dc

        End If

    Next dc

    Set max = cc

End Function

Public Function min() As cCell

    Dim cc As cCell, dc As cCell

    For Each dc In Rows

        If cc Is Nothing Then

            Set cc = dc

        ElseIf dc.Value < cc.Value Then

            Set cc = dc

        End If

    Next dc

    Set min = cc

End Function

I'm not so sure about a sum or average and other mathematical type formulas though, since there are decisions to be made (and therefore behavior catered for) about what to do about non-numeric or otherwise invalid cells. 

bruce

--
You received this message because you are subscribed to the Google Groups "Excel Ramblings" group.
To post to this group, send email to excel-r...@googlegroups.com.
To unsubscribe from this group, send email to excel-ramblin...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/excel-ramblings?hl=en.

[EGMono]

I had time to check out and test your suggestion today, and I
discovered that Empty is less than zero. If a cell is missing a value,
it becomes the Min. This is a bit different than what I had expected,
as Excel skips empty cells. I also tried:
Dim dc as cDataColumn
Set dc = dataset.Columns(1)
minValue =
Application.WorksheetFunctions.Min(dc.where.Resize(dc.Rows.Count))
which worked the way I expected, but takes 3 times as long to execute.
I'm going to poke around with it some more. :)

[Bruce Mcpherson]

Hi
Simple solution is
If not isempty(dc.value) and dc.value < cc.value then
set cc=dc

[EGMono]

That's close (but a bit neater) than what I wound up with, thanks!

[Bruce Mcpherson]

You're welcome!

bruce