The who owes what problem

Hi all, this being my first post be gentle. Let me know if this doesn't make sense or I am over complicating the problem.

I have been struggling to get the output i want from a single formula and the problem goes as follows.

Total owed = 1 1 1 1 1 1 1 1 1 1 = 10

Person A = 1 1 2 0 2 1 2 1 0 1 = 11

Person B = 1 1 0 0 0 0 0 0 0 0 = 2


Person A owes the Total 2 (Because Person A is yet to pay 4th and 9th payment to the Total)
Person B owes the Total 8 (Because Person B is yet to pay 3rd to 10th payments to the Total)
Person B owes person A 2 (Because Person A payed for Person B twice)

The values payed by Person A and B are always half of each other (i.e Person B payed 100, Person A payed 0, so A owes B 50 and the Total 50) Ideally i would like 4 cell outputs:

Person A owes Total x amount
Person B owes Total x amount
Person A owes Person B x amount
Person B owes Person A x amount

1. How is this data (the 1s and 0s) stored?

All in the same cell?
Across a series of cells on the same row?
Some other method?

2. How many occurrences of these 1s and 0s are you going to have on a row? You have 10 for both Person A and Person B. Will there be more?
If there are more will they extend to the right of the existing data?

Originally Posted by Kidsan

Hi K,

Sorry i couldn't work out hot to put cells in the message.

All data is in separate cells. Total in column A, Person A in column B and Person B in column C.
The values are in separate rows down to the sum of the values added together

2. How many occurrences of these 1s and 0s are you going to have in a column? You have 10 for both Person A and Person B. Will there be more?
If there are more will they extend down the column of the existing data?

Just use COUNTIF()

Person A owes Total x amount
=COUNTIF(B1:B10,0)

Person B owes Total x amount
=COUNTIF(C1:C10,0)

Person A owes Person B x amount
=COUNTIF(C1:C10,2)

Person B owes Person A x amount
=COUNTIF(B1:B10,2)

If you are using values that are not 1 or 2 then you need to have the price stored somewhere, say D1
else you won't know if Person A has paid just for their own or paid for B's as well.

Person A owes Person B x amount
=COUNTIFS(C1:C10=2*D$1,B1:B10,0)

Person B owes Person A x amount
=COUNTIFS(B1:B10=2*D$1,C1:C10,0)

Images arent much help, to test any solution EVERYONE has retype the data again.

If you want us to see what it looks like attach a sample spreadsheet.
Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

This is not clear.

In C5 the cost is 100 and A has paid 200 B nothing so presumably A has paid Bs amount.

But in C10 the cost is 10 B has paid 10 but A has paid 20. Why has A overpaid?

Also, i one example neither A nor B pay anything!
So if A overpays the cost and B pays nothing that doesnt necessarily mean A had paid B's amount.

What exactly are they paying for?A meal? A running total?

Ignore sorry misread the question

Isn't it the sum of the 2 totals divided by 2?
In this case both A & B owe money