Solved

# difference between numbers in one cell

Posted on 2011-03-12
464 Views
I am working in Excel 2010. I have cells with numbers in them, for example, one cell might contain:

1 2 4 7 10 11

I want the difference between those numbers in a cell. How do I do that?

For example, the result of the above would look like this: 1 2 3 3 1 which is the difference between one number and the next.

I am looking for the easiest way to achieve this.

Thanks!
0
Question by:keks_
• 10
• 6
• 5

LVL 10

Expert Comment

Here is a macro which will do what you describe on the activecell - and put the result in the column to the right of the active cell

``````Sub differences()
With ActiveCell
nums = Split(.Value, " ")
For Each nvalue In nums
If i > 0 Then
If i > 1 Then
newvalue = newvalue & " " & nvalue - nums(i - 1)
Else
newvalue = newvalue & nvalue - nums(i - 1)
End If
End If
i = i + 1
Next
.Offset(0, 1).Value = newvalue
End With
End Sub
``````
0

LVL 13

Expert Comment

Write a macro in the Macro ediitor called "ValDiff" as shown below.

Say the cell where you want the differences to be shown is cell "A5" and the cell where the numbers showing is Cell A1, then in the cell A5 place the formula "=ValDiff(A1)"

Hope that is what you are after.
``````Option Explicit
Public Function ValDiff(oCell As Range) As String
Dim nVal As Integer
Dim nVals As Integer
Dim nVals2 As Integer
Dim cRet As String
Dim cVals() As String
Dim cVals2() As String
cVals = Split(oCell, " ")
nVals = UBound(cVals)
For nVal = 0 To nVals
If Len(cVals(nVal)) > 0 Then
nVals2 = nVals2 + 1
ReDim Preserve cVals2(nVals2)
cVals2(nVals2) = cVals(nVal)
End If
Next
For nVal = 2 To nVals2
cRet = Trim(cRet + CStr(Val(cVals2(nVal)) - Val(cVals2(nVal - 1)))) + " "
Next
ValDiff = Trim(cRet)
End Function
``````
0

LVL 13

Expert Comment

Note that this formula can be used ANYWHERE in a spreadsheet, and is not restricted to placing the calculated value in the cell immediately to the right of the activecell (which is what happens in the code from Macrini.

I am not criticing Macrinis code in any way. Although I have not tested it, I am sure it performs the action required if you values are to be placed in the cell immediately adjacent to the cell holding the values.

My code though gives added flexibility in that the values can be placed ANYWHERE  - execpt in the cell holding the orignal values, ofcourse  :-)
0

LVL 13

Expert Comment

Note that this formula can be used ANYWHERE in a spreadsheet, and is not restricted to placing the calculated value in the cell immediately to the right of the active cell (which is what happens in the code from Macrini.

I am not criticising code in any way. Although I have not tested it, I am sure it performs the action required if you values are to be placed in the cell immediately adjacent to the cell holding the values.

My code though gives added flexibility in that the values can be placed ANYWHERE  - except in the cell holding the original values, ofcourse  :-)
``````Option Explicit
'Always delare this option - forces good programming standards and avoids bugs

'FUNCTION VALDIFF()
'This function takes the values listed in a cell (oCell) and calculates the difference
'(in sequence) of each number in the cell, listing the differences (again in order) in a different cell
'(e.g. if cell "D2" contains the formula "=ValDiff(A2)" then the values calculated by this
' formula will be displayed in Cell D2
Public Function ValDiff(oCell As Range) As String

'Parameter:  oCell - The address of the Cell being examined (e.g. A1)-do not use quotation marks
'                    since this parameter is NOT a string

Dim nVal As Integer          ' A Loop counter
Dim nVals As Integer         ' The number of items in the cell under examination
Dim nVals2 As Integer        ' Another Loop counter
Dim cRet As String           ' A temporary holder for the value to eventually be returned by this function
Dim cVals() As String        ' An array of the iyems listed in the "Host" cell (oCell)
Dim cVals2() As String       ' A second array holding the values in cVals() but ignoring any "blank"
' values picked up due to too many spaces being used between charaters

'First lets get the values from the reference cell, where the characters are seperated by a single space
cVals = Split(oCell, " ")

'Calculate the number of values found
nVals = UBound(cVals)

'We need to weed out values which were stored as "blank" because there where spaces
'comprising of more than one "blank". This is in case user places two spaces (or more) between
For nVal = 0 To nVals
'ignore any values woth zero length
If Len(cVals(nVal)) > 0 Then
'keep track of the number of valid items found
nVals2 = nVals2 + 1
'redimension the new array to hold valid values
ReDim Preserve cVals2(nVals2)

'now store the latest valid value in the latest cell of the new array
cVals2(nVals2) = cVals(nVal)
End If
Next

'starting at the second valid value, go through each value in the new array
For nVal = 2 To nVals2
'and add to the cRet variable the numerical difference between the value being examined
'the the value of the PREVIOUS item in the array of valid values.
'Each new "difference" found is then seperated from the next by appending a "space" (" ")
cRet = Trim(cRet + CStr(Val(cVals2(nVal)) - Val(cVals2(nVal - 1)))) + " "
Next

'Trim the final built up listing to remove the final "space"
'and assign it to the function name, allowing the function to return this value (cRet)
'to the calling Cell
ValDiff = Trim(cRet)
End Function
``````
0

LVL 13

Expert Comment

I meant to say "I am not criticising Macrini's code in any way" in the previous posting.

Sorry about that, I am using QuickEE and it is having some problems with Spell check at the moment
0

LVL 13

Expert Comment

By the way, if you simply "Copy/Paste" the formula into other cells, it will adjust using relative cell references.

So (say) in Cell A5 you have "=ValDiff(A1)" and you copy it into A6, then the formula should
adjust to "=ValDiff(B1)" and instantly display the changed values (after pressing to refresh)
0

LVL 10

Expert Comment

Never a concern critiquing my code.  That code was quick and dirty to give an example so that the OP had something to go on.  Its absolutely not my finest work and more than happy for someone else to write it better
0

Author Comment

Thank you both so much. I am really not an expert at Excel so I am looking for something really simple.

Craisin, I tried your formula but it didn't work for me. Probably my fault, but I don't have the skills to troubleshoot it. If you would like to include more instructions, I will be happy to try again.

Makrini, yours worked. I select the cell that has the numbers, I click Macros and run, and the cell next to it has the resulting values.

Makrini, how do I run it for multiple cells? I have a column of 1000+ values to run it on, and right now it doesn't work when I select multiple cells, I have to do each individually. How do I run the macro for all cells?
0

LVL 10

Expert Comment

Try this small modification

``````Sub differences()
Dim Cell As Variant
Dim i As Long

For Each Cell In Selection
With Cell
nums = Split(.Value, " ")
For Each nvalue In nums
If i > 0 Then
If i > 1 Then
newvalue = newvalue & " " & nvalue - nums(i - 1)
Else
newvalue = newvalue & nvalue - nums(i - 1)
End If
End If
i = i + 1
Next
.Offset(0, 1).Value = newvalue
End With
Next Cell
End Sub
``````
0

LVL 13

Expert Comment

My code works perfectly in Excel 2003.

What version of excel are you using?

1. Go into Tools/Macro/Visual Basic Editor.
2. Click on "Insert/Module" on the menu then showing in the macro window.
3. Copy all my code into that module.
4. Go back to your worksheet.
5. In a cell type "=ValDiff(A1)" and press enter
(I am assuming you have your data in Cell A1)

Works perfectly...please try again and let me know how you go.
If still not working I will send you a complete tutorial.

0

LVL 13

Expert Comment

I just noticed you are using 2010.

That should make no difference and my code should still work.

What error messages are you getting?
0

LVL 10

Expert Comment

Craisin's solution is a good one here because you can use it in a flexible manner.  Note it is a "Function" rather than a "Sub" so you hav to use a formula (as described) to use it.

There is nothing in the code that is overly complex or that will not work in 2010.  Just looks a little busier.  If you are using other code you will find the "option explicit" at the top is probably stopping your other code working, but that is a good thing if you want to learn good practice. (I usually turn option explicit on when I am close to finishing my code as I like to fiddle first befoe cleaning)

0

Author Comment

Makrini, when I select multiple cells with the new code I get

Run-time error '9':
subscript out of range
0

LVL 10

Accepted Solution

Makrini earned 400 total points
Small error - fixed below

``````Sub differences()
Dim Cell As Variant
Dim i As Long

For Each Cell In Selection
i = 0
newvalue = ""
With Cell
nums = Split(.Value, " ")
For Each nvalue In nums
If i > 0 Then
If i > 1 Then
newvalue = newvalue & " " & nvalue - nums(i - 1)
Else
newvalue = newvalue & nvalue - nums(i - 1)
End If
End If
i = i + 1
Next
.Offset(0, 1).Value = newvalue
End With
Next Cell
End Sub
``````
0

LVL 13

Assisted Solution

Chris Raisin earned 100 total points
keks,

Have you retried my code?

I am a little disappointed it does not work for you, since I put a lot of effort into documentation and getting it to perform well under any conditions.

Simply place your values in any cell then in another cell tyep "=ValDiff(xx)",
where "xx" is the cell in which you have placed the data.

You will notice that even if data is MOVED, the values in the formula do not change!  I suspect the other option submitted by Makrini may not work if the data moves position.

By the way, thank you Makrini for your kind words. You are quite correct about the "Option Explicit". I always have it turned ON to avoid any erroneous programming logic creeping in. Perhaps that is why keks is having a problem, as you say. If the "Option Explicit" is removed or commented out, perhaps it will work. But as you say, it SHOULD really be turned on, and then any errors in any existing code should then be removed. For "Quick and Dirty" remove "Option Explicit".

Keks, try removing "Option Explicit" and see if that helps.

In the attached spreadsheet, Cqells B1, C1 and D1 have the "differences"
while cells A1, A3 and A12 have the data. Try "Cut" then "Paste" of "A12"  into
"B14" and see how the value of the formula changes in "D1" from "=ValDiff(A12)" to "=ValDiff(B14)" and the actual calculated values do NOT change.

Also extra spaces in A12 between numbers do not affect calculations.

Let me know how it goes.

Cheers  :-)
Chris
(craisin)

ValDiffs.xls
0

Author Comment

hey thanks so much, two quick questions if you don't mind:

1. i have the results as 1 3 12 5 for example, when i try to sort them, it gives me 1 12 3 5. How can I make it sort things right?

2. could you please provide code if I wanted the first number appended to the beginning? For example, if the first number was 2 in the previous example, it would give me 2 1 3 12 5

THANKS!
0

Author Comment

0

Author Closing Comment

Thanks to both. Honestly Makrini's quick and dirty code suited my novice skills best.

Thanks!
0

LVL 13

Expert Comment

The code is not hiding, but it is a bit tricky to get to (designed that way by Microsoft I guess).

I just happened to have purchased Office 2010 TODAY, since I was frustrated thinking that I could not help you fully with your version

I bought it through eBay and received the FULL professional package (all 7 applications) which normally cost \$800 in the stores (here in Australia) for
\$150 - the real thing too!  Wow!

Anyway, I installed it and  I opened within Excel 2010 the spreadsheet which I have supplied you. I have never seen Office 2010 before....looks great!

I then had a crash course at learning the interface (trial and error) and worked out how you get to the macros (much easier than through Excel 2003, believe me).

Attached is procedure to get to the code.

In Excel 2010, under the "View" menu, the Macros are only used to "Record" new ones, run existing ones or delete existing ones.

To edit or view the actual code, go via the "Developer" Tab which you have to make visible (you can also create/delete/edit" macros from the developer tab.)

Full details on Macros is available from the Help System:
http://office.microsoft.com/client/helppreview14.aspx?

It all worked beuatifully under 2010 as well!  :-)

Hope this was of assistance to you. Thanks for share of points.

By the way, in answer to your query to Makrini, new questions must be opened in a new thread available to all. I will certainly be glad to help out as well.   :-)
(I need the points since I am 60 years of age now and don't have too many years left in me!)  LOL

Cheers
Chris
(craisin)

Macros-in-Excel-2010-1.jpg
Macros-in-Excel-2010-2.jpg
Macros-in-Excel-2010-3.jpg
Macros-in-Excel-2010-4.jpg
Macros-in-Excel-2010-5.jpg
Macros-in-Excel-2010-6.jpg
Macros-in-Excel-2010-7.jpg
Macros-in-Excel-2010-8.jpg
0

LVL 10

Expert Comment

Another Aussie!  I am in Brisbane
0

LVL 13

Expert Comment

Great!

I survived the Tsunami in Toowoomba - drove away from there 15 minutes before it struck while visitng my Mum. She survived too (many didn't) :-(
0

## Featured Post

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Coâ€¦
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Datâ€¦
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a â€¦
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.