Average "IF" Follow-On Question

[carl]

for your help on my initial question on setting up
an "average if" function. Here's the ARRAY function I am
using...=AVERAGE(IF(A1:A100<>0,A1:A100)).

Next Question:

Can the forumla also perform the averaging if cell B1:B100
equals D1,D2,D3 ?. For example.

Column A has prices and Column B has Products. Cells D1,
D2, D3 has my list of 3 Products. So I would like to put
the average formula into cell E1, E2, and E3 that will
look at the prices in Col A and show me the average price
per product.

Thank you in advance.

 

[Jason Morin]

=AVERAGE(IF((A1:A100<>0)*(B1:B100=D1),A1:A100))

Array-entered.

HTH
Jason
Atlanta, GA

 

[Peo Sjoblom]

One way

=AVERAGE(IF((A1:A100<>0)*(B1:B100=D1)+(B1:B100=D2)+(B1:B100=D3),A1:A100))

array entered

 

[Frank Kabel]

Hi Peo

not sure but sholdn't that be
=AVERAGE(IF((A1:A100<>0)*((B1:B100=D1)+(B1:B100=D2)+
(B1:B100=D3)),A1:A100))

 

[Peo Sjoblom]

Correct

--

Regards,

Peo Sjoblom

Hi Peo

not sure but sholdn't that be
=AVERAGE(IF((A1:A100<>0)*((B1:B100=D1)+(B1:B100=D2)+
(B1:B100=D3)),A1:A100))