Calculate discount amt with conditions

[cprao]

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]

small correction done

 

[John C]

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?

 

[cprao]

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.