Forum Discussion
IF calculation
I don't have much experience with excel. I have 3 individuals who owe a 4th individual, each owes a separate amount. So it would be
Richard Doe is owed a total of $1,000
John Doe owes up to $1,000
Jane Doe owes up to $750
James Doe owes up to $500.
Richard Doe can't receive more than $1,000. Example: Jane Doe owes $750 or until Richard receives $1000.
Can someone help with this?
bclukey wrote: ``the context is debt collection, and no one wants to pay, plus the debtors often don't have much money``
I understand.
But you still do not understand that without additional criteria, the solution is arbitrary. For example, each could pay 1000/3 =333, 333 and 334.
Perhaps you want something like the following:
Enter the amounts into B1 and B2.
But that presumes a "priority" for lower payments. For example, John can pay the least.
Alternatively, If we replace an amount in B2 with a formula of the form =MIN(1000, B1), John would indeed pay the entire amount, unless the amount owed to Richard (B1) exceeds the max that John will pay -- as I wrote previously.
Furthermore, if we replace the formula in B3 with the least of the remainder that James pays (1), Jane will be forced to pay the remainder -- up to her max.
And if we replace the formula in B4 with the least of the remainder that Jane pays (1), the total of the payments might be less than the amount owed (B1), resulting in a nonzero remainder (B5).
5 Replies
You need to provide more criteria. Otherwise, why can't John pay the full $1000, and Jane and James pay zero?
If this is coursework (it sounds like it), please post the entire and exact text of the assignment. I suspect that you have misinterpreted the problem.
- Sorry, the problem is that the context is debt collection, and no one wants to pay, plus the debtors often don't have much money. If John could pay $1,000 that would be great, but it rarely happens.
bclukey wrote: ``the context is debt collection, and no one wants to pay, plus the debtors often don't have much money``
I understand.
But you still do not understand that without additional criteria, the solution is arbitrary. For example, each could pay 1000/3 =333, 333 and 334.
Perhaps you want something like the following:
Enter the amounts into B1 and B2.
But that presumes a "priority" for lower payments. For example, John can pay the least.
Alternatively, If we replace an amount in B2 with a formula of the form =MIN(1000, B1), John would indeed pay the entire amount, unless the amount owed to Richard (B1) exceeds the max that John will pay -- as I wrote previously.
Furthermore, if we replace the formula in B3 with the least of the remainder that James pays (1), Jane will be forced to pay the remainder -- up to her max.
And if we replace the formula in B4 with the least of the remainder that Jane pays (1), the total of the payments might be less than the amount owed (B1), resulting in a nonzero remainder (B5).
