If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculate discount amt with conditions
Bill date qty Bill amt Pay.date Amt Bal. Days Disc@ Dis.Amt
18-Jul 18 800 19-Jul 900 -100 1 4 72 20-Jul 18 900 19-Jul 200 700 -1 0 0 20-Jul 0 0 24-Jul 700 0 4 3 54 30-Jul 18 900 28-Jul 400 500 -2 0 0 30-Jul 0 0 4-Aug 600 100 5 2 36 As given in the above tabel I wish to calculate the Disc Amt if the Days are below 7 days with various disc.rates for various days if bal.is =0. Disc Amt is qty multiply by Disc.rate. Please help with excel worksheet function. Thanks in advance -- cprao |
#2
|
|||
|
|||
Calculate discount amt with conditions
small correction done
-- cprao "cprao" wrote: Bill date qty Bill amt Pay.date Amt Bal. Days Disc@ Dis.Amt 18-Jul 18 800 19-Jul 900 -100 1 4 72 20-Jul 18 900 19-Jul 200 700 -1 0 0 20-Jul 0 0 24-Jul 700 0 4 3 54 30-Jul 18 900 28-Jul 400 500 -2 0 0 30-Jul 0 0 4-Aug 600 -100 5 2 36 As given in the above tabel I wish to calculate the Disc Amt if the Days are below 7 days with various disc.rates for various days if bal.is =0. Disc Amt is qty multiply by Disc.rate. Please help with excel worksheet function. Thanks in advance -- cprao |
#3
|
|||
|
|||
Calculate discount amt with conditions
Perhaps you need to share a little more information. Your data structure
looks inconsistent. For example, you show a -100 balance on line 1, but on line 2, you show bill of 900, payment of 200, and balance of 700, then line 3 you show no bill, but payment of 700, you show balance of 0 now, so it is obvious you are combining the balance of line 2 with pay amount of line 3, but what about the -100 balance from line 1? Also, the Discount rates of 4, 3, 2, how exactly are 4, 3, and 2 determined? It appears you are trying to give a discount if an order is 'prepaid' or paid for at time of pickup/delivery, by your comment of days=0, but then how does the final payment of 700 noted by line 3 get discounted when the payment is received after the line 2 order was pickedup/delivered? Do you have formulas? -- John C "cprao" wrote: small correction done -- cprao "cprao" wrote: Bill date qty Bill amt Pay.date Amt Bal. Days Disc@ Dis.Amt 18-Jul 18 800 19-Jul 900 -100 1 4 72 20-Jul 18 900 19-Jul 200 700 -1 0 0 20-Jul 0 0 24-Jul 700 0 4 3 54 30-Jul 18 900 28-Jul 400 500 -2 0 0 30-Jul 0 0 4-Aug 600 -100 5 2 36 As given in the above tabel I wish to calculate the Disc Amt if the Days are below 7 days with various disc.rates for various days if bal.is =0. Disc Amt is qty multiply by Disc.rate. Please help with excel worksheet function. Thanks in advance -- cprao |
#4
|
|||
|
|||
Calculate discount amt with conditions
Sorry I gave it in a hurry. Thanks for the interest. The balance is a running
balance which will be adjusted against payment or bills amount pending. As for the discount there is a formula which gives discount if the payment is within 2 days it's 4/- and within 5 days it's 3/- and for within 6 days it's 2/-. If the payment is beyond 6 days then no discount for the said material which has unique invocie/bill no. Hence here first come first payment will be adjusted to bills accordingly and any payments satisfiies the above no. of days criteria then it the said qty qualifies for discount. Hope I am able to clarify you point. Please reply. Thanks in advance. -- cprao "John C" wrote: Perhaps you need to share a little more information. Your data structure looks inconsistent. For example, you show a -100 balance on line 1, but on line 2, you show bill of 900, payment of 200, and balance of 700, then line 3 you show no bill, but payment of 700, you show balance of 0 now, so it is obvious you are combining the balance of line 2 with pay amount of line 3, but what about the -100 balance from line 1? Also, the Discount rates of 4, 3, 2, how exactly are 4, 3, and 2 determined? It appears you are trying to give a discount if an order is 'prepaid' or paid for at time of pickup/delivery, by your comment of days=0, but then how does the final payment of 700 noted by line 3 get discounted when the payment is received after the line 2 order was pickedup/delivered? Do you have formulas? -- John C "cprao" wrote: small correction done -- cprao "cprao" wrote: Bill date qty Bill amt Pay.date Amt Bal. Days Disc@ Dis.Amt 18-Jul 18 800 19-Jul 900 -100 1 4 72 20-Jul 18 900 19-Jul 200 700 -1 0 0 20-Jul 0 0 24-Jul 700 0 4 3 54 30-Jul 18 900 28-Jul 400 500 -2 0 0 30-Jul 0 0 4-Aug 600 -100 5 2 36 As given in the above tabel I wish to calculate the Disc Amt if the Days are below 7 days with various disc.rates for various days if bal.is =0. Disc Amt is qty multiply by Disc.rate. Please help with excel worksheet function. Thanks in advance -- cprao |
Thread Tools | |
Display Modes | |
|
|