excel formula to determine possible answers

July 17, 2010 at 16:35:40
Specs: Windows XP
Is there a formula to help me with the following example: I receive a check from a customer to pay their statement. They have invoice amounts that total $8,345.26 and the check is for $4,322.01. Is there a formula where I can list all of the invoice amounts and plug in a total and get possible solutions to equal that total.

See More: excel formula to determine possible answers

Report •

July 17, 2010 at 17:17:29
I think you need to supply some more details.

The obvious (and probably wrong) answer is to take the total of all the invoices and subtract the amount paid so far.

The result will be a possible solution to equal that total.

Why not provide a few more pieces of example data so that we can better understand what you are trying to do.

Report •

July 17, 2010 at 18:10:55
EX: I have the following amounts
I receive a payment of $2,235.57
Is there a formula that can tell me which amounts in the list would be possible solutions to equal $2,235.57

Report •

July 17, 2010 at 19:21:34
No, there is no formula that will do what you ask.

It's possible that it could be done with a macro, but I'm not even sure about that. Getting close is fairly easy, but hitting an exact number could take numerous, numerous, numerous iterations through the list of numbers.

Before we even go down that path, I have a question of my own:

Don't your customers tell you what they are paying for? If I sent someone $2,235.57 to pay off some invoices, I'd have come up with that number based on specific invoices. I'd tell the payee which invoices to apply the payment to. If I didn't, how could I make sure that your records matched mine?

Report •

Related Solutions

July 18, 2010 at 07:48:35

Another comment/question,

Do customers pay the oldest first.

If your list of amounts had dates attached, could you add the amounts, by date until the total was reached.

Alternatively (and this is a business solution, not an Excel solution), always apply any payments received to the oldest invoices first.


Report •

July 18, 2010 at 08:56:32
yes, my customers do tell me what they are paying for--that was just the example I was using. Recently the state updated their processing equipment and since that time the deposits going into my account don't match the daily ebt (food stamp) transactions. They provide no daily detail report, so I have to go through a process of elimination to determine which tickets are not being paid so that I can try to isolate the problem. I'm pretty good at it, but its time consuming to say the least. You have to have as much info as possible before you contact someone working in a gov't agency. 1 out of every 5 calls lends itself to someone who can help me! I was hoping someone had this awesome formula that would crunch the numbers for me instead of having to use my brain.

Report •

Ask Question