1. 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_cf s__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?

Excel statistical functions

Cheers,

3. If the Discharge_cfs__Mean, when present, is always greater than 0, you can use.
=AVERAGE(IF((Date>=\$D3)*(Date<=\$E3)*(Discharge_cfs __Mean>0),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.

4. 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

5. 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!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•