Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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?
    Attached Files Attached Files

  2. #2
    Star Lounger
    Join Date
    Sep 2002
    Location
    Hastings, Sussex, England
    Posts
    67
    Thanks
    0
    Thanked 1 Time in 1 Post
    Have a look at this article:


    Excel statistical functions

    I found it very helpful.

    Cheers,

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #5
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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
  •