Getting the difference between a "negative" number and a positive


M

Mike Cadena

Hi All,
I'm trying to get the difference between two cells. Example A1=50
B1=9999850. Trying to subtract A1-B1. The numbers are from a ne
mechanical counter that starts at 9999850 then goes up to 0 then it
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 tim
occurrence but its on multiple cells
 
Ad

Advertisements

M

Mazzaropi

Mike Cadena, good evening.

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

Is it what you want
 
Ad

Advertisements

J

joeu2004

Mike Cadena said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top