#1   Report Post  
Denise
 
Posts: n/a
Default 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   Report Post  
B. R.Ramachandran
 
Posts: n/a
Default

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   Report Post  
Gary''s Student
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
with formulas that show negative results I want to show zero inste brit64 Excel Discussion (Misc queries) 6 August 29th 05 11:12 PM
Negative Values Only [email protected] Excel Discussion (Misc queries) 1 August 8th 05 08:58 PM
Why "datedif" function results sometimes negative numbers? Ambrosiy Excel Worksheet Functions 1 July 8th 05 11:29 AM
calculating results in formulas Linda Excel Discussion (Misc queries) 9 July 6th 05 09:20 AM
problem:search results negative in excel docs with macros spirit39 Excel Discussion (Misc queries) 0 February 11th 05 12:01 AM


All times are GMT +1. The time now is 03:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"