Average "IF" Follow-On Question

  • Thread starter Thread starter carl
  • Start date Start date
C

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.
 
=AVERAGE(IF((A1:A100<>0)*(B1:B100=D1),A1:A100))

Array-entered.

HTH
Jason
Atlanta, GA
 
One way

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

array entered
 
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))
 
Correct

--

Regards,

Peo Sjoblom


Frank Kabel said:
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))
Click to expand...
 
Back
Top