Formula Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
Click to expand...
1 ACTIVE 11.88% _______ Average
 
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
 
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
 
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.
 
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.
 
Back
Top