alisaheb wrote:
I have following set of data in my Excel worksheet.

Column A - Todays Sales Value
Column B - Todays Payment

Based on above, followings columns are configured for
Column C - Opening Bal
Column D - Cumulative Purchases
Column E - Cumulative Payments

Now in following columns I desire to get

Column F - Payment for Todays Sale
Column G - Advance Payment Received
Column H - Payment for Old Dues

What best function I can put in each column.
Thanks in advance.


Assuming headings in row 1, these are your formulas for row 2 and down:

D2=IF(AND(A2="",B2=""),"",SUM($A$2:A2))
E2=IF(AND(A2="",B2=""),"",SUM($B$2:B2))
F2=IF(AND(A2="",B2=""),"",MIN(A2,B2-H2))
G2=IF(AND(A2="",B2=""),"",B2-F2-H2)
H2=IF(AND(A2="",B2=""),"",MAX(0,MIN(B2,C2)))

and row 3 and down:

C3=IF(AND(A3="",B3=""),"",C2+A2-B2)