Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Default Getting the difference between a "negative" number and a positive

Hi All,
I'm trying to get the difference between two cells. Example A1=500 B1=9999850. Trying to subtract A1-B1. The numbers are from a new mechanical counter that starts at 9999850 then goes up to 0 then its positive from then on. Trying to get the result of 650 not -9999350. =ABS(A1-B1) dosen't do it. It's killing me since its only a one time occurrence but its on multiple cells.
  #2   Report Post  
Senior Member
 
Location: Belo Horizonte, Brazil
Posts: 170
Default

Mike Cadena, good evening.

Try to use this one:
=IF(A1<B1,A1+10000000,A1-B1)

Is it what you want?
__________________
I hope it can help you.

Best regards,
Marcilio Lobão
---------------------------
Belo Horizonte, Brazil
  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 829
Default Getting the difference between a "negative" number and a positive

"Mike Cadena" wrote:
I'm trying to get the difference between two cells. Example A1=500
B1=9999850. Trying to subtract A1-B1. The numbers are from a new
mechanical counter that starts at 9999850 then goes up to 0 then its
positive from then on. Trying to get the result of 650 not -9999350.


You say that it "goes up to 0" from 9999850. I assume you mean it is
7-digit counter that goes from 9999850 to 9999851 to 9999852 ... to 99999999
to 0000000 to 0000001 ... to 9999850.

If so, then use either of the equivalent formulas:

=IF(A1<=B1, 10000000+A1-B1, A1-B1)
or
=A1-B1+10000000*(A1<=B1)

Thus, if A1 is 9999999, the count is 149 (9999999-9999850); and if A1 is
zero, the count is 150 (10000000+0-9999850).

Note that if A1 is 9999850, testing A1<=B1 instead of A1<B1 treats the
difference as 10000000, not zero. I think that makes sense.

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
calculate the difference between negative and positive values watty Excel Worksheet Functions 7 May 20th 23 03:45 AM
How to Show "+" for positive and "-" for negative Emil Excel Worksheet Functions 10 November 5th 08 03:20 PM
Delete "CR" at the end of a number and make the # negative? [email protected] Excel Discussion (Misc queries) 5 August 16th 07 05:19 PM
exel"negative and positive days for creditors :formula kent Excel Discussion (Misc queries) 1 October 3rd 06 11:06 AM
macro for converting number stored as "text" (or preceeded with ') to "number" formatting markx Excel Programming 1 June 23rd 06 03:15 PM


All times are GMT +1. The time now is 11:17 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"