Find nth non-zero number in range, from right to left..

[JonesyUK]

Hi All,

Please can you suggest a formula to return the nth non-zero number in a row of data, starting from the right.

Example: the 5th non-zero number in the following string, starting from the right would be 108:

52, 40, 108, 5, 58, 0, 22

Thanks for your help!

 

[Fluff]

Using Office 365 (it says version 2008??)

In that case please update your profile to show 365.

 

[KP117]

Try this at B2,
=INDEX(4:4,LARGE(IF(B4:S4=0,"",COLUMN(B4:S4)),B1))

 

[JonesyUK]

Try this at B2,
=INDEX(4:4,LARGE(IF(B4:S4=0,"",COLUMN(B4:S4)),B1))

Thanks, this works! Appreciate it very much...

 

[Fluff]

Did you try what Joe or I suggested?

 

[Joe4]

Did you try what Joe or I suggested?

I thought maybe our posts when invisible or something...

 

[JonesyUK]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

With a formula, how about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	5
2	40	52	40	108	5	58	0	22
3	58	52	40	108	5	58	11	22	0	15	6
4	108	52	40	108	5	58	0	22	0	0	9
Data

Cell Formulas
Range	Formula
A2:A4	A2	=INDEX(B2:X2,AGGREGATE(14,6,(COLUMN(B2:X2)-COLUMN(B2)+1)/(B2:X2>0),$A$1))

hi, thanks, this one looks like the text is corrupted....??

 

[Fluff]

hi, thanks, this one looks like the text is corrupted....??

In what way? It looks fine to me.

 

[Dave Patton]

N.B. KP117's suggestion requires Array Enter (CSE) for versions prior to 365.

 

[motombo]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

With a formula, how about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	5
2	40	52	40	108	5	58	0	22
3	58	52	40	108	5	58	11	22	0	15	6
4	108	52	40	108	5	58	0	22	0	0	9
Data

Cell Formulas
Range	Formula
A2:A4	A2	=INDEX(B2:X2,AGGREGATE(14,6,(COLUMN(B2:X2)-COLUMN(B2)+1)/(B2:X2>0),$A$1))

Hi,

Thank you for the solution, I have been trying to figure out how to change the formula to read from left to right rather, any assistance will be greatly appreciated.

 

[Fluff]

Please start a thread of your own as it is significantly different from the OP.
When you do, please post some sample data.