Results 1 to 5 of 5

Thread: Help with Sum formula

  1. #1

    Help with Sum formula



    Register for a FREE account, and/
    or Log in to avoid these ads!


    Help with sum formula

    Hello,

    I am new to excel and and it would be amazing if anyone could help me out.

    I have around 2000 rows with a set of 8 different numbers in each row. For instance,

    2-4-7-11-15-29-35-36
    4-16-20-29-32-37-49-50
    etc...

    Is it possible to be able to get if any 2 numbers in each line add up to a 3rd number?

    For instance, in

    1) 4-7-11-12-40-42-56-83, 4 + 7 add up to 11.

    So I would like to get the sum 11.

    2) If there are more two pairs of numbers that add up to a third number, for instance, in 1-2-4-16-20-29-49-210, 4 + 16 add up to 20 and 20+29 add up to 49, I would like to get again as a result the two sums.

    If the above is not possible, please suggest any other way that I can get an indication that the lines have the above characteristic. For instance instead of getting the sums, get the number 1 instead of 11 as in the first example and get the number 2 when two pairs add up to a sum as in the second example.

    I hope someone can help me out.

    Thank you

    Nasp

  2. #2
    Whether the data contain in a row i.e. 2-4-7-11-15-29-35-36 is in different column or a single column?

  3. #3
    Yes this can be done. There are 2 ways that I can think of to do it but if you are new to excel it may be tricky. First you have to identify all number combinations that can give you your total... So if you are looking at numbers that total 8 you would have 1+7, 2+6, 3+5 and 4+4. you can then use a combination of if and find statments to identify if it is present. Post some source data and I will have a look
    Last edited by RichDenman; 2011-12-15 at 01:47 PM.

  4. #4
    The below works for me as a user defined function. Since you could return multiple results you can pass an optional argument that specifies which number of iteration it returns. Ie you would call it as =findSums(A1,3) where 3 is the third match.

    The code:

    Code:
    Function findSums(s As String, n As Integer) As String
    
    
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim numberCount As Integer
    Dim numberInstance As Integer
    Dim numberFactors As Variant
    Dim tempSum As Integer
    
    
    numberInstance = 0
    numberFactors = Split(s, "-")
    numberCount = UBound(numberFactors)
    
    
    For i = 0 To numberCount
        For j = i To numberCount
            For k = 0 To numberCount
                If i = j Then
                ElseIf CInt(numberFactors(k)) = CInt(numberFactors(i)) + CInt(numberFactors(j)) Then
                    numberInstance = numberInstance + 1
                    If numberInstance = n Then
                        findSums = numberFactors(i) & " & " & numberFactors(j) & " add to equal " & numberFactors(k)
                    End If
                End If
            Next k
        Next j
    Next i
    
    
    End Function

  5. #5
    I glazed over the part where you said you're new to excel. Check out this link for info on using a UDF and where to put the code, http://www.cpearson.com/Excel/Writin...ionsInVBA.aspx. If you post a sample workbook I can add the code and repost.

    Jesse

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •