Solved

difference between numbers in one cell

Posted on 2011-03-12
21
464 Views
Last Modified: 2012-05-11
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
Comment
Question by:keks_
  • 10
  • 6
  • 5
21 Comments
 
LVL 10

Expert Comment

by:Makrini
Comment Utility
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
 
LVL 13

Expert Comment

by:Chris Raisin
Comment Utility
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
 
LVL 13

Expert Comment

by:Chris Raisin
Comment Utility
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
 
LVL 13

Expert Comment

by:Chris Raisin
Comment Utility
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
 
LVL 13

Expert Comment

by:Chris Raisin
Comment Utility
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:Chris Raisin
Comment Utility
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

by:Makrini
Comment Utility
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

by:keks_
Comment Utility
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

by:Makrini
Comment Utility
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
 
LVL 13

Expert Comment

by:Chris Raisin
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 13

Expert Comment

by:Chris Raisin
Comment Utility
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

by:Makrini
Comment Utility
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

by:keks_
Comment Utility
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

by:
Makrini earned 400 total points
Comment Utility
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
 
LVL 13

Assisted Solution

by:Chris Raisin
Chris Raisin earned 100 total points
Comment Utility
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
 

Author Comment

by:keks_
Comment Utility
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

by:keks_
Comment Utility
craisin, i downloaded your xls. It works also. However when I click Macros I don't see anything? Where is your code hiding?
0
 

Author Closing Comment

by:keks_
Comment Utility
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
 
LVL 13

Expert Comment

by:Chris Raisin
Comment Utility
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

by:Makrini
Comment Utility
Another Aussie!  I am in Brisbane
0
 
LVL 13

Expert Comment

by:Chris Raisin
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now