Formula Help
 

The below formula was provided for the question below, can someone tell me
how to add into the formula how to not include cells missing information so
that it does take those cells into account when calculating the average in
cell C1?

=SUMIF(A1:A7,"Active",B1:B7)/COUNTIF(A1:A7,"Active")

I have a question that I hope someone can help with.

A B C

1 ACTIVE 11.88% _______ Average
2 ACTIVE
3 ACTIVE 12.80%
4 ACTIVE
5 ACTIVE 14.67%
6 INACTIVE 22.90%
7 ACTIVE 45.09%

If I wanted to get an average in cell C1 of all the cells in Column B1:B5
that read active in Column A1:A5 how would I write that formula?

Formula Help
 

There are a few things that you can do. The most simple would be to change
the cells in column A to "Inactive" for those rows in column B that are empty.

A B C

1 ACTIVE 11.88% _______ Average
2 INACTIVE
3 ACTIVE 12.80%
4 INACTIVE
5 ACTIVE 14.67%
6 INACTIVE 22.90%
7 ACTIVE 45.09%

The second way would be to insert a new column between B & C and fill the
following formula down for the entire range:
=IF(AND(A1="Active",OR(B1<"",B1<0)),B1,"")
That formula basically says, if the cell is Active and the value isn't 0 or
empty, then return the original value, otherwise return a zero-length string.
Then, instead of the SumIF statement that you were using, you can simply use
the AVERAGE function on the new range of numbers.
=AVERAGE(C1:C7)

A B C
D

1 ACTIVE 11.88% 11.88%
_______ Average
2 ACTIVE
3 ACTIVE 12.80% 12.80%
4 ACTIVE
5 ACTIVE 14.67% 14.67%
6 INACTIVE 22.90%
7 ACTIVE 45.09% 45.09%

Hope this helps.

-Cory

"Excel for Dummies" wrote:

The below formula was provided for the question below, can someone tell me
how to add into the formula how to not include cells missing information so
that it does take those cells into account when calculating the average in
cell C1?

=SUMIF(A1:A7,"Active",B1:B7)/COUNTIF(A1:A7,"Active")

I have a question that I hope someone can help with.

A B C

1 ACTIVE 11.88% _______ Average
2 ACTIVE
3 ACTIVE 12.80%
4 ACTIVE
5 ACTIVE 14.67%
6 INACTIVE 22.90%
7 ACTIVE 45.09%

If I wanted to get an average in cell C1 of all the cells in Column B1:B5
that read active in Column A1:A5 how would I write that formula?

Formula Help
 

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=AVERAGE(IF((A1:A7="Active")*(ISNUMBER(B1:B7)),B1: B7))

Format as %

Biff

"Excel for Dummies" wrote in
message ...
The below formula was provided for the question below, can someone tell
me
how to add into the formula how to not include cells missing information
so
that it does take those cells into account when calculating the average in
cell C1?

=SUMIF(A1:A7,"Active",B1:B7)/COUNTIF(A1:A7,"Active")

I have a question that I hope someone can help with.

A B C

1 ACTIVE 11.88% _______ Average
2 ACTIVE
3 ACTIVE 12.80%
4 ACTIVE
5 ACTIVE 14.67%
6 INACTIVE 22.90%
7 ACTIVE 45.09%

If I wanted to get an average in cell C1 of all the cells in Column B1:B5
that read active in Column A1:A5 how would I write that formula?

Formula Help
 

If you need to leave those cells Active, then you'll have to go with the
second solution presented - use another column that checks if it is Active
and not blank. Then use the AVERAGE function on that new column.

"Excel for Dummies" wrote:

I need to leave those cells ACTIVE, because they are showing that the account
is still active, but we are just waiting on information to insert in the
blank cells. So, I just need to add something to the formula that will
discard the cells missing information from the average calucation, until
there is information inserted. So another words a running average
calculation. Right now it is adding the cells missing information when
calculating the average of column B, and it is providing me an inaccurate
average.


"Cory" wrote:

There are a few things that you can do. The most simple would be to change
the cells in column A to "Inactive" for those rows in column B that are empty.

A B C

1 ACTIVE 11.88% _______ Average
2 INACTIVE
3 ACTIVE 12.80%
4 INACTIVE
5 ACTIVE 14.67%
6 INACTIVE 22.90%
7 ACTIVE 45.09%

The second way would be to insert a new column between B & C and fill the
following formula down for the entire range:
=IF(AND(A1="Active",OR(B1<"",B1<0)),B1,"")
That formula basically says, if the cell is Active and the value isn't 0 or
empty, then return the original value, otherwise return a zero-length string.
Then, instead of the SumIF statement that you were using, you can simply use
the AVERAGE function on the new range of numbers.
=AVERAGE(C1:C7)

A B C
D

1 ACTIVE 11.88% 11.88%
_______ Average
2 ACTIVE
3 ACTIVE 12.80% 12.80%
4 ACTIVE
5 ACTIVE 14.67% 14.67%
6 INACTIVE 22.90%
7 ACTIVE 45.09% 45.09%

Hope this helps.

-Cory

"Excel for Dummies" wrote:

The below formula was provided for the question below, can someone tell me
how to add into the formula how to not include cells missing information so
that it does take those cells into account when calculating the average in
cell C1?

=SUMIF(A1:A7,"Active",B1:B7)/COUNTIF(A1:A7,"Active")

I have a question that I hope someone can help with.

A B C

1 ACTIVE 11.88% _______ Average
2 ACTIVE
3 ACTIVE 12.80%
4 ACTIVE
5 ACTIVE 14.67%
6 INACTIVE 22.90%
7 ACTIVE 45.09%

If I wanted to get an average in cell C1 of all the cells in Column B1:B5
that read active in Column A1:A5 how would I write that formula?

Formula Help
 

I need to leave those cells ACTIVE, because they are showing that the account
is still active, but we are just waiting on information to insert in the
blank cells. So, I just need to add something to the formula that will
discard the cells missing information from the average calucation, until
there is information inserted. So another words a running average
calculation. Right now it is adding the cells missing information when
calculating the average of column B, and it is providing me an inaccurate
average.


"Cory" wrote:

There are a few things that you can do. The most simple would be to change
the cells in column A to "Inactive" for those rows in column B that are empty.

A B C

1 ACTIVE 11.88% _______ Average
2 INACTIVE
3 ACTIVE 12.80%
4 INACTIVE
5 ACTIVE 14.67%
6 INACTIVE 22.90%
7 ACTIVE 45.09%

The second way would be to insert a new column between B & C and fill the
following formula down for the entire range:
=IF(AND(A1="Active",OR(B1<"",B1<0)),B1,"")
That formula basically says, if the cell is Active and the value isn't 0 or
empty, then return the original value, otherwise return a zero-length string.
Then, instead of the SumIF statement that you were using, you can simply use
the AVERAGE function on the new range of numbers.
=AVERAGE(C1:C7)

A B C
D

1 ACTIVE 11.88% 11.88%
_______ Average
2 ACTIVE
3 ACTIVE 12.80% 12.80%
4 ACTIVE
5 ACTIVE 14.67% 14.67%
6 INACTIVE 22.90%
7 ACTIVE 45.09% 45.09%

Hope this helps.

-Cory

"Excel for Dummies" wrote:

The below formula was provided for the question below, can someone tell me
how to add into the formula how to not include cells missing information so
that it does take those cells into account when calculating the average in
cell C1?

=SUMIF(A1:A7,"Active",B1:B7)/COUNTIF(A1:A7,"Active")

I have a question that I hope someone can help with.

A B C

1 ACTIVE 11.88% _______ Average
2 ACTIVE
3 ACTIVE 12.80%
4 ACTIVE
5 ACTIVE 14.67%
6 INACTIVE 22.90%
7 ACTIVE 45.09%

If I wanted to get an average in cell C1 of all the cells in Column B1:B5
that read active in Column A1:A5 how would I write that formula?