find 2nd last value (number) in row greater than 0

[APML]

Hi all.. I'm a little stuck and would appreciate any help....
Lets say I have a range from A1 to J1 and I want to find the 2nd last number in the row that's greater than 0, i.e see below, I'm trying to extract 9
Note all these number have been generated from formula's in the cells.

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1	2	3	5	7	0	5	9	3	0	0
2
3	9
Sheet11

Cell Formulas
Range	Formula
A3	A3	=INDEX(1:1,AGGREGATE(14,6,COLUMN(A1:J1)/(A1:J1<>0),2))

Or:

=LET(f,FILTER(A1:J1,A1:J1<>0),c,COLUMNS(f),INDEX(f,c-1))

if you like the newer functions.

 

[APML]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1	2	3	5	7	0	5	9	3	0	0
2
3	9
Sheet11

Cell Formulas
Range	Formula
A3	A3	=INDEX(1:1,AGGREGATE(14,6,COLUMN(A1:J1)/(A1:J1<>0),2))

Or:

=LET(f,FILTER(A1:J1,A1:J1<>0),c,COLUMNS(f),INDEX(f,c-1))

if you like the newer functions.

Hey wow..haven't used anything like that before.
My actual range is E12 : JD12 (I put A1 : J1 in the example because I thought it would be simpler to explain).. I'll be copying to down a few hundred rows.
With that in mind how do I chage your formula =INDEX(1:1,AGGREGATE(14,6,COLUMN(A1:J1)/(A1:J1<>0),2)) to suit ?

 

[APML]

Hey wow..haven't used anything like that before.
My actual range is E12 : JD12 (I put A1 : J1 in the example because I thought it would be simpler to explain).. I'll be copying to down a few hundred rows.
With that in mind how do I chage your formula =INDEX(1:1,AGGREGATE(14,6,COLUMN(A1:J1)/(A1:J1<>0),2)) to suit ?

Hey fantastic. I worked it out. Thanks very much, you've saved me

 

[Eric W]

Happy to help! Thanks for the feedback.