ARRAY FORMULA QUESTION

Topic

New Reply

I have an apparent problem using array formulas to calculate means, medians, etc. As shown on the attached sheet, I have defined a date range (date1) and a data range (q_1). Within those ranges there are days for which there are no data.

(1) If I use an array formula (cell F2);
e.g., =AVERAGE(IF((Date>=$D$2)*(Date<=$E$2),Discharge_cfs__Mean))
I get a result of 608.77.

(2) If I use (cell G2):
=AVERAGE(B2:B29162)
I get a result of 1137.25.

(3) If I remove all of the blank data points (date2 and q_2), the array formula and normal average agree.

Using ISNUMBER() shows the blank cells to be FALSE and the non-blank cells to be TRUE.

What the heck is going on here?! It appears that the array formula works differently from the standard formula in that it incorporates non-numeric data. Am I correct on this?

Reply | Quote

Replies

Have a look at this article:

Excel statistical functions

I found it very helpful.

Cheers,

Reply | Quote

If the Discharge_cfs__Mean, when present, is always greater than 0, you can use.
=AVERAGE(IF((Date>=$D3)*(Date0),Discharge_cfs__Mean))
or if there would be negative values, you can use
=AVERAGE(IF((Date>=$D3)*(Date<=$E3)*(Discharge_cfs__Mean””),Discharge_cfs__Mean))
If you want to use the ISBLANK, you have to make a Not(IsBlank):
=AVERAGE(IF((A2:A12>=D2)*(A2:A12<=E2)*(NOT(ISBLANK(B2:B12))),B2:B12))

as a side note:
A 1.64 MB file is a bit much. You could have posted a much smaller book that would have illustrated the problem just as well.

Reply | Quote

The key is to only average the numbers if the range may have cells that are non-numeric:
=AVERAGE(IF((A2:A12>=D2)*(A2:A12<=E2)*ISNUMBER(B2:B12),B2:B12))

Arrays do work a little differently. The average in non-arrays was created to count only numbers. In arrays it counts all cells matching the criteria which makes the denominator too large if there are non-numeric cells…

Steve

Reply | Quote

All:

Thank you for your replies. Now I have to go and correct my formulas….fortunately, I noticed the discrepancy while the work was still inhouse.
And thanks for the continuing Excel education.

John

mea culpa on file size…another PEBKAC!

Reply | Quote