HELP ON SUM - REQUIRED

[alisaheb]

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.

 

[Fred Smith]

It looks to me like simple addition and subtraction, but only you can
determine the requirements. Before we can help you, you need to identify:

1. How do you calculate Payment for Todays Sale?
2. How do you calculate Advance Payment Received?
3. How do you calculate Payment for Old Dues?

As is normally the case, examples help a lot.

Regards,
Fred

 

[alisaheb]

Any payment on the day should first get adjusted to old dues if any, then for
todays sale if any, then only to Advance payment.

Example:

Old dues: 1000
Todays sale: 2500
Payments made: 4000

Results will be:

Payment towards Old dues: 1000
Payments for todays sale: 2500
Advance Received: 500

Hope my excample is clear.

Thanks.

 

[Fred Smith]

I don't see where "Old dues" is in any of your columns. Is it to be
calculated from columns C, D and E, and if so, how?

Regards,
Fred.

 

[alisaheb]

Sorry... Opening balance for each customer will be provided manually once,
later it should consider automatically from other data like today's sale,
todays payment ... etc.

 

[Fred Smith]

You're not making it easy to provide help. Is Opening balance the same as
Old dues? If so, where is it -- one of the columns specified, or some other
column?

Regards,
Fred.

Sorry... Opening balance for each customer will be provided manually once,
later it should consider automatically from other data like today's sale,
todays payment ... etc.

 

[Glenn]

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)

 

[alisaheb]

Old dues is outside Opening Balance - probably we can put in same sheet in a
separate column or recall through VLOOKUP & incorporate in Opening Balance

Opening Balance will be = One time Old due (either + or -) + Todays Sale -
Payments Received.

 

[alisaheb]

THanks Glenn, your solution is very close to my requirement. Probably it is
correct one as per my inputs while posting problem. Actually I need one more
suggestion with following corrections.

Infact your solution has helped me in reducing bundling of functions to get
same results.

As I have replied to Fred's queries, there is one more input to be
considered "OLD DUE" which will come either through VLOOKUP from a master
maintained outside this work sheet or we can insert one more column for the
same.

Accordingly my earlier input "OPENING BALANCE" stands changed to "CLOSING
BALANCE". in CLOUMN 'C'.

This is required to maintain daily sales data of regular few customers not
of one single customer.

Please help me.

Hope Fred is also giving me a good solution.

Thanks in advance