ExcelBanter - Formula Help

ExcelBanter (https://www.excelbanter.com/)

- New Users to Excel (https://www.excelbanter.com/new-users-excel/)

- - Formula Help (https://www.excelbanter.com/new-users-excel/120289-formula-help.html)

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?

"Excel for Dummies" wrote in message
...
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

You could use

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

confirmed with CTRL+SHIFT+ENTER

"Gerry Atrick" wrote:

"Excel for Dummies" wrote in message
...
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