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

Discussion in 'Excel Beginners' started by Mike Cadena, Mar 27, 2014.

  1. Mike Cadena

    Mike Cadena Guest

    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


    --
    Mike Cadena
     
    Mike Cadena, Mar 27, 2014
    #1
    1. Advertisements

  2. Mike Cadena

    Mazzaropi Guest

    Mike Cadena, good evening.

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

    Is it what you want


    --
    Mazzaropi
     
    Mazzaropi, Mar 28, 2014
    #2
    1. Advertisements

  3. Mike Cadena

    joeu2004 Guest

    "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.
     
    joeu2004, Mar 28, 2014
    #3
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. fgiord

    negative numbers to positive numbers in excel

    fgiord, Nov 8, 2004, in forum: Excel Beginners
    Replies:
    3
    Views:
    94
    Niek Otten
    Nov 8, 2004
  2. chom

    Compare Negative & Positive Numbers

    chom, Jul 28, 2005, in forum: Excel Beginners
    Replies:
    3
    Views:
    167
  3. George

    Change negative amount to positive.

    George, Jul 29, 2005, in forum: Excel Beginners
    Replies:
    2
    Views:
    131
    George
    Jul 29, 2005
  4. Scotty

    Excel Positive/Negative Running Total

    Scotty, Mar 22, 2007, in forum: Excel Beginners
    Replies:
    1
    Views:
    192
    Pete_UK
    Mar 22, 2007
  5. Alex Vega
    Replies:
    4
    Views:
    165
    AdamV
    May 27, 2007
  6. jay

    Changing positive values to negative

    jay, Jul 6, 2007, in forum: Excel Beginners
    Replies:
    2
    Views:
    138
  7. Nita
    Replies:
    3
    Views:
    215
  8. robbin
    Replies:
    1
    Views:
    88
    Niek Otten
    Jun 17, 2008
Loading...