keks_
asked on
difference between numbers in one cell
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!
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!
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.
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
I have placed a more informative listing of the code below, which now includes comments.
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 :-)
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 :-)
I have placed a more informative listing of the code below, which now includes comments.
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 :-)
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
'characters instead of one.
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
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
Sorry about that, I am using QuickEE and it is having some problems with Spell check at the moment
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)
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)
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
ASKER
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?
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?
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
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.
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.
I just noticed you are using 2010.
That should make no difference and my code should still work.
What error messages are you getting?
That should make no difference and my code should still work.
What error messages are you getting?
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)
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)
ASKER
Makrini, when I select multiple cells with the new code I get
Run-time error '9':
subscript out of range
Run-time error '9':
subscript out of range
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
ASKER
craisin, i downloaded your xls. It works also. However when I click Macros I don't see anything? Where is your code hiding?
ASKER
Thanks to both. Honestly Makrini's quick and dirty code suited my novice skills best.
Makrini could you please answer my 2 additional questions, or do I have to create a new thread?
Thanks!
Makrini could you please answer my 2 additional questions, or do I have to create a new thread?
Thanks!
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
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
Another Aussie! I am in Brisbane
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) :-(
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) :-(
Open in new window