Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Negative Results
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 |
#2
|
|||
|
|||
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 "Denise" wrote: 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 |
#3
|
|||
|
|||
Try:
=IF(AND(Disposition="Home", L4K4),L4-K4,"") This will remove negatives and zero. I have also dropped the xx because the AVERAGE function will conveniently ignore blanks -- Gary''s Student "Denise" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
with formulas that show negative results I want to show zero inste | Excel Discussion (Misc queries) | |||
Negative Values Only | Excel Discussion (Misc queries) | |||
Why "datedif" function results sometimes negative numbers? | Excel Worksheet Functions | |||
calculating results in formulas | Excel Discussion (Misc queries) | |||
problem:search results negative in excel docs with macros | Excel Discussion (Misc queries) |