Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP ON SUM - REQUIRED
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. -- Alisaheb |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP ON SUM - REQUIRED
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" wrote in message ... 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. -- Alisaheb |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP ON SUM - REQUIRED
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. -- Alisaheb "Fred Smith" wrote: 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" wrote in message ... 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. -- Alisaheb |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP ON SUM - REQUIRED
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" wrote in message ... 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. -- Alisaheb "Fred Smith" wrote: 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" wrote in message ... 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. -- Alisaheb |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP ON SUM - REQUIRED
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. "alisaheb" wrote in message ... 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. -- Alisaheb "Fred Smith" wrote: 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" wrote in message ... 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. -- Alisaheb "Fred Smith" wrote: 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" wrote in message ... 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. -- Alisaheb |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP ON SUM - REQUIRED
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) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP ON SUM - REQUIRED
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 "Fred Smith" wrote: 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. "alisaheb" wrote in message ... 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. -- Alisaheb "Fred Smith" wrote: 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" wrote in message ... 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. -- Alisaheb "Fred Smith" wrote: 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" wrote in message ... 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. -- Alisaheb |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP ON SUM - REQUIRED
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 -- Alisaheb "Glenn" wrote: 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help required. | Excel Discussion (Misc queries) | |||
help required | Excel Worksheet Functions | |||
Help Required | Excel Worksheet Functions | |||
Help Required.... | Excel Discussion (Misc queries) | |||
VB Required?? | Excel Worksheet Functions |