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

--
cprao

--
cprao

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

In article ,
cprao wrote:

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

--
cprao

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


"JE McGimpsey" wrote:

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

In article ,
cprao wrote:

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

--
cprao

Thanks it is working excellently. I have another question as under. Please help
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


"cprao" wrote:

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


"JE McGimpsey" wrote:

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

In article ,
cprao wrote:

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

--
cprao