return values in the table with multiple conditions

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

JE
Wed Jul 23 22:23:53 CDT 2008

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 <748AF79B-76E0-4BA6-8704-4DD889B1D945@microsoft.com>,
cprao <cprao@discussions.microsoft.com> 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

cprao
Thu Jul 24 10:12:00 CDT 2008

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 <748AF79B-76E0-4BA6-8704-4DD889B1D945@microsoft.com>,
> cprao <cprao@discussions.microsoft.com> 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
>

cprao
Thu Aug 07 10:18:01 CDT 2008

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 <748AF79B-76E0-4BA6-8704-4DD889B1D945@microsoft.com>,
> > cprao <cprao@discussions.microsoft.com> 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
> >