1

In Excel, I want to add 2 numbers with missing digits and would like a list of all possible solutions.

For example: Given 5.x1 + 2.y1 = 7.32, what are the possible combinations of x and y?

In this case one combination of (x, y) is (1, 2) since 5.11 + 2.21 = 7.32. Another combination that would work is (2, 1) since 5.21 + 2.11 = 7.32.

Is tried to use Solver, but it only let's me specify variables. I think solver is trying to do x + y = 7.32, which could have infinite number of answers.

I also tried using SUMIFS but the conditions do not allow me to use wildcard for numbers.

Why am I doing this? I copied text from an image with cells, but some of the numbers didn't get pasted correctly as numbers. Instead they were like letters (o for 0, l for 1, but sometimes it does weird things like {} for 0.) The image source is no longer available to check manually, but the totals for the rows and column are legible and correct. Most of the numbers just have 1 digit missing. I could guess them, but would like Excel to help me pick from possible combinations.

0

At first create two columns with all the 100 possibilities for the two digits.

Insert your calculation in the third column, e.g. =5.01+2.01+(A1+B1)/10, here you can filter for the desired result, or you can use a pivot take I'd you don't want to have hidden rows.

0

The key to solving this is to use integer maths.

We re-structure the problem to ask for the solutions to:

5xy+7zw = 732

for example 500+232 = 732

The following macro loops through the possibilities and records the solutions in columns A and B

Sub Peter()
    For i = 0 To 9
        For j = 0 To 9
            For k = 0 To 9
                For l = 0 To 9
                    v1 = CLng("5" & i & j)
                    v2 = CLng("2" & k & l)
                    If v1 + v2 = 732 Then
                        Cells(Z + 1, 1) = v1
                        Cells(Z + 1, 2) = v2
                        Z = Z + 1
                    End If
                Next l
            Next k
        Next j
    Next i
End Sub

There are 33 solutions:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.