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

2. 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
•