return values in the table with multiple conditions

C

cprao

I have a table like this
columnA ColumnB Amount
A1= 10 0 -1.00
A2= 11 10 2.00
A3= 12 0 -5.00
A4= 13 0 -1.50
A5= 14 11 3.00
A6= 0 12 2.00
A7= 0 13 1.00
A8= 0 0 -4.00
A9= 0 14 3.00
A10= 0 0 1.00
In columnB I want to return columnA nos if the following conditions are met.
1)B1:-If amount(C1) is -ve then in B1 should be 0 otherwise it is A1.
2)B2:-if C2 is -ve then B2 is zero,and B1 is zero then return A1 otherwise
A2. I want results like in B column stoping till largest number in colA is
met.
I have tried nested 'if' with no success, Please help. Thanking you in
expectation of early reply
 
J

JE McGimpsey

I'm not sure I understand you correctly, but this produces the results
you give as an example:

B1: = (C1>=0) * A1

B2: =IF(C2<0,0,INDEX(A:A,COUNTIF($B$1:$B1,">0")+1))
 
C

cprao

Thanks it is working. I will let you know after apply it in my reports.
Thanks once again
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top