GET QTY in column J

  • Thread starter Thread starter cprao
  • Start date Start date
C

cprao

Bill date qty Bill amt Pay.date Amt Bal. Days
A B C D E F I J
18-Jul 18 800 19-Jul 900 -100 1 18
20-Jul 18 900 19-Jul 200 600 -1 0
20-Jul 0 0 24-Jul 700 0 4 18
30-Jul 18 900 28-Jul 400 500 -2 0
30-Jul 0 0 4-Aug 600 -100 5 18
As given in the above tabel I wish to GET the qty fig whenever the Days are
below 7 days with various with condition that bal.is <=0. Please help
with excel worksheet function.
Thanks in advance
 
Thank for the reply. I am afraid it is not working. Because what I require is
(qty)B2(i.e.18) in J3 when (Bal due)F3<=0 and (days)I<7. Hope it is clear
now. yours formula gives me zero instead of 18
 
Yes, I misunderstood what you wanted. Try this in J3:

=IF(AND(I3<7,F3<=0),B3,0)

Then copy down the column.

Hope this helps.

Pete
 
I am afraid it is not working. Because what I require is
(qty)B2(i.e.18) in J3 when (Bal due)F3<=0 and (days)I<7. Hope it is clear
now. yours formula gives me zero instead of 18
 
Hi

You have had 2 answers which should have worked, except you say in your note
that your want the value in B2, when you are testing against the values in
I3 and F3.
If this is correct, then just amend the formula to
=IF(AND(I3<7,F3<=0),B2,0)


--
Regards
Roger Govier

cprao said:
I am afraid it is not working. Because what I require is
(qty)B2(i.e.18) in J3 when (Bal due)F3<=0 and (days)I<7. Hope it is clear
now. yours formula gives me zero instead of 18
Click to expand...
 
Hi Peo

I have absolutely no idea.
In his request, he keeps referring to tests on row 3, and getting a result
from B2.
I merely offered this amendment as a solution that satisfied both his
formula and his required result.

Seems very strange to me.
 
Thanks Roger & Peo for the interest. Peo is write. The formula has to work
for evey row. Please be in contact. Thanks in advance
 
I have found the following formula. Can anybody give shorter than this. It
would be helpful.

=IF(AND(F2<=0,G2<7),INDEX(B$2:B$6,IF(COUNTIF(B1:B2,">0")>1,ROW(),ROW()-1))
 
Using the same principle this is three characters fewer:

=INDEX(B$2:B$6,ROW()-1+(B1>0)*(B2>0))*((F2<=0)*(G2<7))

There is no *false* argument in either of the formulas so they both return
FALSE. This one returns 0, which may not suit the OP's requirements.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top