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 600 -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

I'm not really clear on everything you want to do, but I'm guessing you have
multiple embedded if statements. When I have a complicated equation to put
together, I break it down into pieces.

In one cell, calculate the discount amount
In the next cell, calculate whether the discount amount applies.
etc.

Once you have it all working, you can put it all together.
--
HTH,
Barb Reinhardt



"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 600 -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

Thanks for interest. I just want to return the qty if the condition that the
final payment is received within 7 days. For ex. for 30 july inv.final
payment is on 4 aug. Hence I want qty(columnB) multiply by Disc rate(columnH)
in ColumnI.
--
cprao


"Barb Reinhardt" wrote:

I'm not really clear on everything you want to do, but I'm guessing you have
multiple embedded if statements. When I have a complicated equation to put
together, I break it down into pieces.

In one cell, calculate the discount amount
In the next cell, calculate whether the discount amount applies.
etc.

Once you have it all working, you can put it all together.
--
HTH,
Barb Reinhardt



"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 600 -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