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

General discussion

Excel help

Aug 19, 2005 12:52AM PDT

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

- Collapse -
(NT) (NT) The if function?
Aug 19, 2005 3:05AM PDT
- Collapse -
an example would be
Aug 19, 2005 5:39AM PDT

something like:
=IF(A1+B1+C1+D1=15,"Yup is 15","Nope not 15")

- Collapse -
A VBA routine?
Aug 19, 2005 5:40AM PDT

It might help if you explain with an example what you want.

Kees

- Collapse -
Here's what I mean.
Aug 19, 2005 11:46AM PDT

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

- Collapse -
Checking combinations
Aug 19, 2005 1:19PM PDT

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

- Collapse -
Still a relatively simple IF statement
Aug 20, 2005 5:49AM PDT

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.

- Collapse -
Are those numbers random?
Aug 20, 2005 7:57AM PDT

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

- Collapse -
Combinations
Aug 21, 2005 9:07AM PDT

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 Happy !

Frank