MS Excel - Cells to Find Summary

I am looking for an Excel function that identifies any combination of cells in a colum which when summarized will equate to a given number.  For example in the attached spreadsheet, the given is 177.41.  I need to find what combination of numbers equate to that.  The simple answer is 29.26 plus 148.15 (highlighted yellow in the attached).  But it could be a longer list and it could be a different combination of summarized numbers - not just two.

Is there such a function?
2014-300.216505-GL-SAMPLE.xlsx
LVL 1
CFMIFinancial Systems AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
No.

What you would have to do is a binary SUMIF - you would have an extra column which would show zero (don't include in the total) and one (include in the total). Then you would loop through all of the possibilities to see which of them would add up to the correct total.

However, as you have 141 different values, then the total number of possibilities would 2 to the power of 141 - that's a number with 42 digits. You can reduce that number by sorting them in order, and then pruning the tree when you get over the amount.
0
CFMIFinancial Systems AnalystAuthor Commented:
"You can reduce that number by sorting them in order, and then pruning the tree when you get over the amount."  You're suggesting that the tree gets pruned by small to large.  Of course that's not always the case so besides the fact that your "2 to the power of 141" answer suggests we're looking for two numbers, I see how no combination that big is really possible.  I'll chew on this and either close if we're done looking or respond differently if we can tweak / simplify the question.  Thank you.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
No, 2 to the power of 141 does not suggest two numbers. That would be 141 * 140. 2^141 suggests every single permutation of numbers.

1st permutation - all no.
2nd permutation - all no, except the first one.
3rd permutation - all no, except the second one.
4th permutation - all no, except the first and second one.
5th permutation - all no, except the third one.
6th permutation - all no, except the third and first one.

etc.

If the tree is ordered by small to large, then once you are over the number, then you don't need to keep adding to the current list, and can stop there.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CFMIFinancial Systems AnalystAuthor Commented:
I apologize for the long delay.  It's agreed this is a hopeless concept.  Thanks for your thoughtful responses.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.