How to calculate Min,Median,Max values in non consecutive rows via

[Pete]

Hi,

I would like to calculate the Min, Median and Max values in non consecutive
rows via VBA.

Year Value
2007 10
2007 20
2008 11
2009 12
2007 30
2008 25
2009 55
2008 26
2008 16
2009 40

I would like to represent the results as follows:

2007 Min = xxx
Median = xxx
Max = xxx

2008 Min = xxx
Median = xxx
Max = xxx

2009 Min = xxx
Median = xxx
Max = xxx

Any idea what my code would look like?

Pete.

 

[Gary''s Student]

You can calculate your statistics (even non-consecutive) without any VBA:

=MAX(IF(A1:A10=2007,B1:B10,""))
=MIN(IF(A1:A10=2007,B1:B10,""))
=MEDIAN(IF(A1:A10=2007,B1:B10,""))

and will display:

30
10
20


These are array formulae. They must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.

If you must use VBA, then use it to insert the equations!

 

[Pete]

Thanks Gary's Student.

I might have over simplified my example. My IF condiction is a little more
involved than just a year. Here is what I currently have.

******************
'Check each Row in Target range for dates
' between FY start and FY end dates
For RowOffset = 1 To Target.Rows.Count
'check if Submission date in Row is not blank and,
'check if Submission date in Row is greater or equal to FY start date and,
'check if Submission date in Row is less or equal to FY end date and,
'check if variance in row is not blank.
If (Target(RowOffset, 5) <> "" And _
Target(RowOffset, 5) >= StartDate And _
Target(RowOffset, 5) <= EndDate And _
Target(RowOffset, 22) <> "") Then

'if the dates are in range then increase DrugCount and add the
variance value
DrugCount = DrugCount + 1
DrugAverage = DrugAverage + Target(RowOffset, 22)

'if the variance is 0 or less then increase the AdhereCount
If Target(RowOffset, 22) <= 0 Then
AdhereCount = AdhereCount + 1

End If

End If
Next RowOffset

'Calculate the average
If DrugCount = 0 Then
DrugAverage = 0
Else: DrugAverage = DrugAverage / DrugCount

End If

'Calculate the Adherence %
If AdhereCount = 0 Then
AdherePct = 0
Else: AdherePct = (AdhereCount / DrugCount) * 100

End If


DrugVarianceSub = DrugAverage & " " & "days" & " " & "(" & AdherePct & "%)"

**************************

However, the 'Average' value is not what the boss wants. They want Min,
Median and Max values.

How can I incorporate that in the above code?

 

[Jerry W. Lewis]

You still don't need VBA; just expand on the conditions in the array formulas

=MAX(IF((A1:A10>=DATEVALUE("11/15/2006))*(A1:A10<=DATEVALUE("4/15/2007)),B1:B10))
=MIN(IF((A1:A10>=DATEVALUE("11/15/2006))*(A1:A10<=DATEVALUE("4/15/2007)),B1:B10))
=MEDIAN(IF((A1:A10>=DATEVALUE("11/15/2006))*(A1:A10<=DATEVALUE("4/15/2007)),B1:B10))

will calculate the corresponding statistics for all dates within the
specified range, and will execute much faster than the corresponding VBA.

Note that the ,"" alternative in original IF functions are not necessary
since they would naturally be a BOOLEAN value which the statatistics
functions would ignore anyway.

Jerry

 

[Pete]

Thanks Jerry, I tried the expanded conditions and it worked.

Thank you both for our speedy replies.

Pete.