Negative Results

D

Denise

I have a spreadsheet that has about 3500 rows. I have one column that has a
formula subtracting certain times. On some of the results I get a negative
result because some of the times are missing.

Here is my formula:

=IF(Disposition="Home",L4-K4,"xx")

L4 is often left blank because they don't have the time.

I need to do an average of these times but the average will not work because
of some of the negative results. Is there a way around this?

Denise
 
B

B. R.Ramachandran

Hi,

Supposing your formula that subtracts times is in Column M (say M2 to M3501).
In some other cell (where the average would go) enter the following formula:

=SUMIF(M2:M3501,">="&0)/COUNTIF(M2:M3501,">="&0),
if you want to include the cells in Column M that may contain zeros for
calculating the average,

or

=SUMIF(M2:M3501,">"&0)/COUNTIF(M2:M3501,">"&0),
if you do not want to include zero-containing cells for calculating the
average

Regards,
B. R. Ramachandran
 
G

Gary''s Student

Try:

=IF(AND(Disposition="Home", L4>K4),L4-K4,"")

This will remove negatives and zero. I have also dropped the xx because the
AVERAGE function will conveniently ignore blanks
 
Top