Formula Help

[Excel for Dummies]

I need help with this formula.

A B C

1 ACTIVE 11.78% ______ AVERAGE
2 ACTIVE 12.88%
3 ACTIVE 11.88%
4 INACTIVE 27.99%
5 INACTIVE 49.78%
6 ACTIVE 55.89%

If I want to calculate the average in cell C1 of all the cells in B1:B6 that
read ACTIVE in cells A1:A6. how should the formula read?

 

[Gerry Atrick]

I need help with this formula.

A B C

1 ACTIVE 11.78% ______ AVERAGE
2 ACTIVE 12.88%
3 ACTIVE 11.88%
4 INACTIVE 27.99%
5 INACTIVE 49.78%
6 ACTIVE 55.89%

If I want to calculate the average in cell C1 of all the cells in B1:B6 that
read ACTIVE in cells A1:A6. how should the formula read?

This may not be the most elegant of solutions - but it works. Formula in C1
would be

=SUMIF(A1:A6,"ACTIVE",B1:B6)/COUNTIF(A1:A6,"ACTIVE")

You should also ensure that cell C1 is formatted as % (percentage), and that the
required number of decimal places are displayed.

Hope this is of some help.


Gerry

 

[daddylonglegs]

You could use

=AVERAGE(IF(A1:A6="Active",B1:B6))

confirmed with CTRL+SHIFT+ENTER