Remember Me?

 Mike Cadena Junior Member First recorded activity by ExcelBanter: Mar 2014 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.

 Mazzaropi Senior Member First recorded activity by ExcelBanter: Jul 2010 Location: Belo Horizonte, Brazil Posts: 170 Mike Cadena, good evening.

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

Is it what you want?
__________________

Best regards,
Marcilio Lobão
---------------------------
Belo Horizonte, Brazil
 joeu2004[_2_] external usenet poster First recorded activity by ExcelBanter: Jul 2011 Posts: 829 Getting the difference between a "negative" number and a positive

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.

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Emil Excel Worksheet Functions 10 November 5th 08 02:20 PM [email protected] Excel Discussion (Misc queries) 5 August 16th 07 05:19 PM watty Excel Worksheet Functions 6 October 19th 06 07:30 PM kent Excel Discussion (Misc queries) 1 October 3rd 06 11:06 AM markx Excel Programming 1 June 23rd 06 03:15 PM

All times are GMT +1. The time now is 02:32 AM. Copyright ©2004-2019 ExcelBanter.