Excel math puzzle
December 14, 2010 8:50 AM Subscribe
Excel question. Is there a way to determine a list of all possible values from a list that add up to a given total?
posted by xedrik to Computers & Internet (4 answers total) 2 users marked this as a favorite
Sort of like the math puzzle "I have 26 coins in my pocket totaling 17 dollars, how many of each coin are there?" Our stupid, outdated Point-Of-Sale system has some serious shortcomings, one of which is no remittance advice on statements. So it becomes a chore for our bookkeeper, when he receives a check for $231.68 to try to figure out which combination of invoices on an account totals up to that amount. It's mostly trial and error.
Is there a way to set up a spreadsheet where you could enter a series of dollar amounts down a column, and then enter a remittance amount in another cell, and then have it calculate which combination of those dollar amounts adds up to the remittance amount?
My level of Excel macro experience is probably medium; I have developed several sheets with deeply nested if/then statements pulling data from several sheets etc. But I have no idea where to start with something like this. :(