Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 27th 14, 10:51 PM
Junior Member
 
First recorded activity by ExcelBanter: Mar 2014
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  
Old March 28th 14, 01:28 AM
Senior Member
 
First recorded activity by ExcelBanter: Jul 2010
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  
Old March 28th 14, 06:03 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
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
How to Show "+" for positive and "-" for negative Emil Excel Worksheet Functions 10 November 5th 08 02: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
calculate the difference between negative and positive values watty Excel Worksheet Functions 6 October 19th 06 07:30 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 02:32 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017