Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    array formula question

    I just noticed that SUM and AVERAGE don't appear to work the same in array formulas. As shown in the attached spreadsheet, the 'hardwired' sum is the same as the array SUM, but the averages are different. What am I missing here?
    Thank.s
    Attached Files Attached Files

  2. #2
    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 ARRAY average does not work as you have done it, since while the Array SUM is accurate, the count being used in the average is all the values. The average being calculated is the ArraySum / 2191 no matter how many meet the criteria. You are creating an array from the A part of TRUEs (the correct year) and FALSEs (incorrect year). When multiplied by the B col, the TRUEs act as 1s and the FALSEs are calc as zero. This does not affect the sum, but the count is inaccurate, making the average inaccurate.

    Use an array formula like:
    =AVERAGE(IF(YEAR($A$15:$A$2205)=A7,B15:B2205))

    This will only the average (sum and count) the values meeting the criteria.

    Steve

Posting Permissions

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