Hi Stefi,

I have tried your formula and it works to a point but when there is more
than one booking for the same person on different dates then the formula
doesn't calculate the correct amount.
What we need to match is the name, unit, start and end. (columns b, c, d, e
in the data below). I have added more data to show the example.

Also once the booking is "paid" I want every balance for that booking to
show "paid". see the last 2 rows of data below. Can this be done?

regards NBA

"Stefi" wrote:

If your data are sorted by name and date like in your example, then try this:

=INDIRECT("F"&MATCH(B2,B:B,0))-SUM(INDEX(G:G,MATCH(B2,B:B,0)):INDEX(G:G,ROW()))

Or, if you insist on displaying "paid" instead of 0 balance:

=IF(INDIRECT("F"&MATCH(B2,B:B,0))-SUM(INDEX(G:G,MATCH(B2,B:B,0)):INDEX(G:G,ROW()))=0 ,"paid",INDIRECT("F"&MATCH(B2,B:B,0))-SUM(INDEX(G:G,MATCH(B2,B:B,0)):INDEX(G:G,ROW())))

Regards,
Stefi

€žnba€� ezt Ã*rta:

I have data in the following columns:
a b c d e f g h
date name unit start end booking payment balance
01/06/09 John Smith 1 1/10/09 10/10/09 $1000 $100 $900
01/07/09 John Smith 1 1/10/09 10/10/09 $500 $400
15/09/09 John Smith 1 1/10/09 10/10/09 $400 paid
01/04/09 Mary Jane 4 1/11/09 20/11/09 $2000 $1000 $1000
21/05/09 Mary Jane 4 1/11/09 20/11/09 $1000 paid
01/10/09 Fred Mac 2 1/12/09 02/12/09 $200 $200 paid
01/10/09 John Smith 9 1/12/09 5/12/09 $900 $100 paid
01/11/09 John Smith 9 1/12/09 5/12/09 $800 paid

I need to create a balance sheet for each booking calculating the amount
outstanding. Each time a payment is made a new line is created showing the
date and the amount paid. This is then subtracted from the initial booking
and you are left with the remaining balance owing or the text "paid".

I have tried nested if statements but it is getting too confusing to sort it
out as the number of payments can vary from 1 to many payments.

Can you help simplify a formula to solve this problem.

I see your problem, but unfortunately up to now I couldn't find a formula
solution for it, for the moment I can think of only a VBA solution!

Stefi


€žnba€� ezt Ã*rta:

Hi Stefi,

I have tried your formula and it works to a point but when there is more
than one booking for the same person on different dates then the formula
doesn't calculate the correct amount.
What we need to match is the name, unit, start and end. (columns b, c, d, e
in the data below). I have added more data to show the example.

Also once the booking is "paid" I want every balance for that booking to
show "paid". see the last 2 rows of data below. Can this be done?

regards NBA

"Stefi" wrote:

If your data are sorted by name and date like in your example, then try this:

=INDIRECT("F"&MATCH(B2,B:B,0))-SUM(INDEX(G:G,MATCH(B2,B:B,0)):INDEX(G:G,ROW()))

Or, if you insist on displaying "paid" instead of 0 balance:

=IF(INDIRECT("F"&MATCH(B2,B:B,0))-SUM(INDEX(G:G,MATCH(B2,B:B,0)):INDEX(G:G,ROW()))=0 ,"paid",INDIRECT("F"&MATCH(B2,B:B,0))-SUM(INDEX(G:G,MATCH(B2,B:B,0)):INDEX(G:G,ROW())))

Regards,
Stefi

€žnba€� ezt Ã*rta:

I have data in the following columns:
a b c d e f g h
date name unit start end booking payment balance
01/06/09 John Smith 1 1/10/09 10/10/09 $1000 $100 $900
01/07/09 John Smith 1 1/10/09 10/10/09 $500 $400
15/09/09 John Smith 1 1/10/09 10/10/09 $400 paid
01/04/09 Mary Jane 4 1/11/09 20/11/09 $2000 $1000 $1000
21/05/09 Mary Jane 4 1/11/09 20/11/09 $1000 paid
01/10/09 Fred Mac 2 1/12/09 02/12/09 $200 $200 paid
01/10/09 John Smith 9 1/12/09 5/12/09 $900 $100 paid
01/11/09 John Smith 9 1/12/09 5/12/09 $800 paid

I need to create a balance sheet for each booking calculating the amount
outstanding. Each time a payment is made a new line is created showing the
date and the amount paid. This is then subtracted from the initial booking
and you are left with the remaining balance owing or the text "paid".

I have tried nested if statements but it is getting too confusing to sort it
out as the number of payments can vary from 1 to many payments.

Can you help simplify a formula to solve this problem.

Stefi,

I am using the following formula for my solution but it has a circular
error. Can you see the problem?

d9 = name, d9 = unit, m9= booking, n9 = payment, o9 = balance (same data as
below)

IF(AND(D9=D10,F9=F10,OR(M9="",N9="")),
IF(M9-N9-N10=0," pif",
IF(O9-N9=0," pif",O9-N9)),
IF(M9-N9=0," pif",
IF(AND(D9=D10,F9=F10,N9+N10=M9)," pif",
IF(AND(D9=D10,D10=D11,F9=F10,F10=F11,N9+N10+N11=M9 )," pif",
IF(M9=""," pif",M9-N9)))))

"Stefi" wrote:

I see your problem, but unfortunately up to now I couldn't find a formula
solution for it, for the moment I can think of only a VBA solution!

Stefi


€žnba€� ezt Ã*rta:

Hi Stefi,

I have tried your formula and it works to a point but when there is more
than one booking for the same person on different dates then the formula
doesn't calculate the correct amount.
What we need to match is the name, unit, start and end. (columns b, c, d, e
in the data below). I have added more data to show the example.

Also once the booking is "paid" I want every balance for that booking to
show "paid". see the last 2 rows of data below. Can this be done?

regards NBA

"Stefi" wrote:

If your data are sorted by name and date like in your example, then try this:

=INDIRECT("F"&MATCH(B2,B:B,0))-SUM(INDEX(G:G,MATCH(B2,B:B,0)):INDEX(G:G,ROW()))

Or, if you insist on displaying "paid" instead of 0 balance:

=IF(INDIRECT("F"&MATCH(B2,B:B,0))-SUM(INDEX(G:G,MATCH(B2,B:B,0)):INDEX(G:G,ROW()))=0 ,"paid",INDIRECT("F"&MATCH(B2,B:B,0))-SUM(INDEX(G:G,MATCH(B2,B:B,0)):INDEX(G:G,ROW())))

Regards,
Stefi

€žnba€� ezt Ã*rta:

I have data in the following columns:
a b c d e f g h
date name unit start end booking payment balance
01/06/09 John Smith 1 1/10/09 10/10/09 $1000 $100 $900
01/07/09 John Smith 1 1/10/09 10/10/09 $500 $400
15/09/09 John Smith 1 1/10/09 10/10/09 $400 paid
01/04/09 Mary Jane 4 1/11/09 20/11/09 $2000 $1000 $1000
21/05/09 Mary Jane 4 1/11/09 20/11/09 $1000 paid
01/10/09 Fred Mac 2 1/12/09 02/12/09 $200 $200 paid
01/10/09 John Smith 9 1/12/09 5/12/09 $900 $100 paid
01/11/09 John Smith 9 1/12/09 5/12/09 $800 paid

I need to create a balance sheet for each booking calculating the amount
outstanding. Each time a payment is made a new line is created showing the
date and the amount paid. This is then subtracted from the initial booking
and you are left with the remaining balance owing or the text "paid".

I have tried nested if statements but it is getting too confusing to sort it
out as the number of payments can vary from 1 to many payments.

Can you help simplify a formula to solve this problem.