Thanks Jerry, I tried the expanded conditions and it worked.
Thank you both for our speedy replies.
Pete.
"Jerry W. Lewis" wrote:
> 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" wrote:
>
> > 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?
> >
> >
> >
> >
> > "Gary''s Student" wrote:
> >
> > > 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!
> > > --
> > > Gary''s Student - gsnu200844
> > >
> > >
> > > "Pete" wrote:
> > >
> > > > 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.
|
Similar Threads
