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

  3. Mike Cadena

    joeu2004 Guest

    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

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