Is there a way in Excel to have it evaluate a number of cells to see if any of them sum to a certain value?
-Jim
![]() | Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years. Thanks, CNET Support |
Is there a way in Excel to have it evaluate a number of cells to see if any of them sum to a certain value?
-Jim
Discussion is locked
Suppose the numbers 57, 21, 108, 61, 5, 84, and 99 occur in the range from A1:A7. Suppose then I want to see if any combiation of these numbers equals 217. It may be that no combination equals that.
A series of IF statements, such as suggested by another poster, would require way too many formulas to be worth the time. I could pretty much eyeball it and guess in less time than it would take to write all these formulas. Which is why I'm wanting to know if there is a formula or even a macro I could write that would work.
-Jim
Kees is right; a VBA routine is the only way to go. Your example indicates you would want to check every combination of the seven numbers. The number of tests required then would be the sum from i = 1 to n of the combinations of seven things taken i an a time -- this is 127 tests.
To do this, a subroutine calculating combinations should be embedded in a loop. You didn't indicate if one match is sufficient or if you want all such matches, but this is rather trivial, determining when and how to exit the loop.
Hope this helps
Frank
How about:
=IF(SUM(A1:A7)=217,"That's 217",SUM(A1:A7))
Don't know quite what you want if the value is TRUE or FALSE, but this works, and says "That's 217" when TRUE. Otherwise it just gives the real sum.
And, when you say "any combination", do you mean things like 5 x 57; 99-57+(61/5)x21?
Is that what you mean by "any combination"? If so, even a macro would be incredibly difficult to write. The possible permutations would be many times more than 127.
And if the numbers are liable to change, or the number of entries to be checked increase, the problem magnifies.
What sort of need do you have for this? I'm not prying, just trying to understand the situation.
Mark
I assume he means additive combinations of random numbers, and the macro would be relatively simple. If he does mean the combination of any or all arithmatic operations on the numbers, then you are right, Mark. I would hate to calulate all the combinations, but it could be well up in the thousands. While I probably could write such a macro, I would not want to -- unless I were being paid my consulting rate, of course
!
Frank