Whats wrong with this array formula

  • Thread starter Thread starter Carl_B
  • Start date Start date
C

Carl_B

I have a worksheet with 5 city names in column A and dollar amounts in
column I.
I need to calculate the average amount for each city.
I use the array formula {=AVERAGE(I2:I9176*(A2:A9176="philadelphia"))} and
get 621.45. I also did this using an average value calculation in a Pivot
Table and got the result 1987.54 for Philadelphia. The other 4 cities had
similar discrepancies.

Is there something wrong with the array formula (No I did not type the
brackets) or am I interpreting the two results incorrectly?
Thanks,
Carl
 
{=AVERAGE(I2:I9176*(A2:A9176="philadelphia"))}
Is there something wrong with the array formula
Click to expand...

Yes

Where A2:A9176 does not equal philadelphia, the result of the multiplication
is 0. So, your formula is including any of those 0s in the average.

Try this array formula** :

=AVERAGE(IF(A2:A9176="philadelphia",I2:I9176))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Try it this way:

=AVERAGE(IF(A2:A9176="philadelphia",I2:I9176))

Still array-entered using Ctrl-Shift-Enter.

Hope this helps.

Pete
 
Using
{=AVERAGE(IF((A2:A9176="philadelphia"),(I2:I9176)))}
produces the correct result
Click to expand...

OK, now get rid of the unnecessary parentheses.

Array entered:

=AVERAGE(IF(A2:A9176="philadelphia",I2:I9176))
 
Tue, 16 Feb 2010 10:55:01 -0800 from Carl_B
I have a worksheet with 5 city names in column A and dollar amounts in
column I.
I need to calculate the average amount for each city.
I use the array formula {=AVERAGE(I2:I9176*(A2:A9176="philadelphia"))} and
get 621.45.
Is there something wrong with the array formula
Click to expand...

You're gonna kick yourself. :-)

Consider a row, say row 88, where A88 doesn't equal Philadelphia.
What value is contributed to the average by that row? Right, since
the value of a false condition is 0, that row contributes a zero,
which drags the average down.

Replace the AVERAGE with COUNT and it will be more obvious.
 
:-) I have an issue with over doing the parentheses.
And I realize that more is not necessarily better!

Thank you, and thanks to Pete and Stan as well.
 
Back
Top