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?
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
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?
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 12:47 PM.
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
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
Bookmarks