Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate the difference between negative and positive values | Excel Worksheet Functions | |||
How to Show "+" for positive and "-" for negative | Excel Worksheet Functions | |||
Delete "CR" at the end of a number and make the # negative? | Excel Discussion (Misc queries) | |||
exel"negative and positive days for creditors :formula | Excel Discussion (Misc queries) | |||
macro for converting number stored as "text" (or preceeded with ') to "number" formatting | Excel Programming |