Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Difference in numbers (XP/2003)

    I have a column of alpha-numeric number that look like this:
    __A_______B___
    A 110
    A 125
    A 200
    B 100
    B 130
    There is always a single letter or symbol such as # at the beginning and a space
    after that. The number can be three or four in length. I would like to setup a column B
    with a formula to show the differences in the numbers.
    For example in the first two A 125 A 110 the difference is 15. The next is 75.
    When the first character changes (i.e. A to [img]/forums/images/smilies/cool.gif[/img] it would not count. or equal zero.
    Anyone have a formula that will work?

    Thanks in advance.
    Richard Spring

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Difference in numbers (XP/2003)

    Assuming that the values begin in A1, enter the following formula in B2:
    <code>
    =IF(LEFT(A2,1)=LEFT(A1,1),VALUE(MID(A2,3,100))-VALUE(MID(A1,3,100)),"")
    </code>
    Fill down as far as the data in column A go.
    If you'd rather have 0 than a blank when the first character changes, replace <code>""</code> at the end of the formula with 0.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Difference in numbers (XP/2003)

    Hi Richard

    This can be done with a hidden or intermediary column.

    In the corresponding cell in Column B type =RIGHT(A2,LEN(A2)-FIND(" ",A2)), This will select the numerical value in the string assuming that the numerical value extends past 999 and there is a preceding space as you decribe.

    In the corresponding cell to the value in Column C (starting C2) type =IF(B2-B3<0,-(B2-B3),B2-B3)

    This will provide the difference. I attach a workbook to describe further
    Jerry

  4. #4
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Difference in numbers (XP/2003)

    Thank you Hans - it works great as always.

    One quick questions in the - - VALUE(MID(A2,3,100))-VALUE(MID(A1,3,100) - - what does the 100 accomplish?
    Richard Spring

  5. #5
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Difference in numbers (XP/2003)

    Thank you Jezza.
    This also works just fine. The only difference is one additional step.
    Many options will expand your vision.
    Richard Spring

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Difference in numbers (XP/2003)

    The MID function has 3 arguments:
    string - the text value from which to extract a portion.
    start - the position where you want to start extracting (1 = at the beginning of the text).
    length = number of characters you want to extract; it's OK to specify more characters than there are.

    Since the number part is everything from the third position to the end, I used 100 as an arbitrary value (much) larger than the longest string you expect to encounter. I might have used 20 or 1000 as well.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Difference in numbers (XP/2003)

    BTW, Excel is rather forgiving when performing calculations - if you subtract two string values, it'll convert them to numbers before subtracting. So the formula can be simplified to
    <code>
    =IF(LEFT(A2,1)=LEFT(A1,1),MID(A2,3,100)-MID(A1,3,100),"")</code>

  8. #8
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Difference in numbers (XP/2003)

    I was thinking that 1000 would be better since they do go into the "A 1000" and up level.
    I do not expect they would exceed "A 9999".

    Will try the second formula to see if any difference.

    Thanks for the speedy response.
    Richard Spring

  9. #9
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Difference in numbers (XP/2003)

    The 100 stands for the number of characters, not the value.
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •