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.
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.
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加