Results 1 to 9 of 9
Thread: Difference in numbers (XP/2003)

20071111, 02:03 #1
 Join Date
 Jan 2004
 Location
 Las Vegas, Nevada, USA
 Posts
 345
 Thanks
 1
 Thanked 0 Times in 0 Posts
Difference in numbers (XP/2003)
I have a column of alphanumeric 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

20071111, 02:16 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20071111, 02:28 #3
 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(B2B3<0,(B2B3),B2B3)
This will provide the difference. I attach a workbook to describe furtherJerry

20071111, 02:36 #4
 Join Date
 Jan 2004
 Location
 Las Vegas, Nevada, USA
 Posts
 345
 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

20071111, 02:40 #5
 Join Date
 Jan 2004
 Location
 Las Vegas, Nevada, USA
 Posts
 345
 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

20071111, 02:45 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20071111, 02:48 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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>

20071111, 03:47 #8
 Join Date
 Jan 2004
 Location
 Las Vegas, Nevada, USA
 Posts
 345
 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

20071111, 06:29 #9
 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