Finding nth value over zero in a row

[billdoor]

This is my first time on this site and it is very helpful.

I need help.

can anybody help with a formula that will find the 6th value in a row of 20 values that is over 0.

many thanks

BILLDOOR

[Aladin Akyurek]

I misread your question, whence this edit: It should be:


=INDEX(A1:T1,0,MATCH(1,TRANSPOSE(((A1:T1>0)*(COLUMN(A1:T1)>=6)*(COLUMN(A1:T1)))))+1)

to be array-entered.

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

Aladin



[ This Message was edited by: Aladin Akyurek on 2002-05-01 06:08 ]

[billdoor]

Sorry about the mis-understanding.

I will try again.

I have a row of numbers , i need a formula that will give me the 6th number above zero.

see examples below

Row 1

3,4,0,4,0,5,2,0,6,1,3,5,1,0

the sixth number is 6

Row 2

9,0,8,0,7,0,6,0,5,0,4,0,3,0,2,0,1

the sixth number is 4.

I will also need to be able to change which number I need, i.e I might need the 9th number or the 3rd number in this series

I hope this is easier to understand.
Many thanks to aladin for the last reply,but sorry it does not work.

Regards

Billdoor

[ This Message was edited by: billdoor on 2002-05-01 07:20 ]

[Aladin Akyurek]

On 2002-05-01 07:17, billdoor wrote:
Sorry about the mis-understanding.

I will try again.

I have a row of numbers , i need a formula that will give me the 6th number above zero.

see examples below

Row 1

3,4,0,4,0,5,2,0,6,1,3,5,1,0

the sixth number is 6

Row 2

9,0,8,0,7,0,6,0,5,0,4,0,3,0,2,0,1

the sixth number is 4.

I will also need to be able to change which number I need, i.e I might need the 9th number or the 3rd number in this series

I hope this is easier to understand.
Many thanks to aladin for the last reply,but sorry it does not work.

Regards

Billdoor

[ This Message was edited by: billdoor on 2002-05-01 07:20 ]

OK, no more edit:

Array-enter:

=INDEX(A1:T1,MATCH($A$4,COUNTIF(INDIRECT(ADDRESS(ROW(A1),COLUMN(A1))&":"&ADDRESS(ROW(A1),COLUMN(A1:T1))),">0"),0))

where A4 houses the condtion like 6, meaning the 6th non-zero positief value.

Aladin

[billdoor]

Thanks for that aladin