• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

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!
0
keks_
Asked:
keks_
  • 10
  • 6
  • 5
2 Solutions
 
MakriniCommented:
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

Open in new window

0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
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

Open in new window

0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
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  :-)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Chris Raisin(Retired Analyst/Programmer)Commented:
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  :-)
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

Open in new window

0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
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
 
Chris Raisin(Retired Analyst/Programmer)Commented:
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
 
MakriniCommented:
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
 
keks_Author Commented:
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
 
MakriniCommented:
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

Open in new window

0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
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
 
Chris Raisin(Retired Analyst/Programmer)Commented:
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
 
MakriniCommented:
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
 
keks_Author Commented:
Makrini, when I select multiple cells with the new code I get

Run-time error '9':
subscript out of range
0
 
MakriniCommented:
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

Open in new window

0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
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.

To help you out I have attached a spreadsheet with the macro working as expected.

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
 
keks_Author Commented:
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
 
keks_Author Commented:
craisin, i downloaded your xls. It works also. However when I click Macros I don't see anything? Where is your code hiding?
0
 
keks_Author Commented:
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!
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
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
 
MakriniCommented:
Another Aussie!  I am in Brisbane
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now