Hi EXCEL GURUS!
Good evening. I would like to ask for your expert problem solving for the sheet I'm making to automate and reduce the time I spend on calculating bills.
Here is the scenario:
- There are a number of PERSONS (P1 to P5) who regularly splits the bills.
- On daily basis, they would have a group consumption that would be paid by any one of them. (e.g. Day 1 was paid by P1)
- Some days will be divided between some of the persons only (e.g. Day 2 was paid by P2 but only P1, P2 and P3 will divide the bill.)
On day 1 - it shows P2 to P5 need to pay P1 an amount of "20" each.
On Day 2 - it shows P1 and P3 need to pay P2 an amount of "5" each.
What i want to achieve:
On the BALANCE TABLE, I am trying to compute and balance the amounts paid.
I want to show how much each person has to collect or pay (in negative value) to the other persons. Please ignore the formulas and values mentioned and I marked red. This is where I got really confused.
The values I want to achieve is the following:
C12 = 0, because P1 doesn't need to pay or collect any amount to/from himself
C13 = 25 - 7 = 18
25 is the sum of the amounts paid by P1 for P2, shown in cells C5 (20), O5 (2) and U5 (3)
7 is the sum of the amounts paid by P2 for P1, shown in cells E4 (5) and M4 (2)
Basically I just want to see who owe what amount after all the bills has been entered in the sheet.
HELP..
Bookmarks