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
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.
--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...