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

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.

--
Biff
Microsoft Excel MVP


"Carl_B" wrote in message
...

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

Try it this way:

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

Still array-entered using Ctrl-Shift-Enter.

Hope this helps.

Pete

On Feb 16, 6:55*pm, Carl_B wrote:
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

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

"Carl_B" wrote:


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

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

OK, now get rid of the unnecessary parentheses.

Array entered:

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

--
Biff
Microsoft Excel MVP


"Carl_B" wrote in message
...
Using
{=AVERAGE(IF((A2:A9176="philadelphia"),(I2:I9176)) )}
produces the correct result

"Carl_B" wrote:


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

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

:-) 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.

"T. Valko" wrote:

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

OK, now get rid of the unnecessary parentheses.

Array entered:

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

--
Biff
Microsoft Excel MVP


"Carl_B" wrote in message
...
Using
{=AVERAGE(IF((A2:A9176="philadelphia"),(I2:I9176)) )}
produces the correct result

"Carl_B" wrote:


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


.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Carl_B" wrote in message
...
:-) 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.

"T. Valko" wrote:

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

OK, now get rid of the unnecessary parentheses.

Array entered:

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

--
Biff
Microsoft Excel MVP


"Carl_B" wrote in message
...
Using
{=AVERAGE(IF((A2:A9176="philadelphia"),(I2:I9176)) )}
produces the correct result

"Carl_B" wrote:


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


.