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

JonesyUK

Board Regular
Joined
Oct 11, 2005
Messages
183
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

KP117

New Member
Joined
Oct 22, 2020
Messages
24
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Try this at B2,
=INDEX(4:4,LARGE(IF(B4:S4=0,"",COLUMN(B4:S4)),B1))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,609
Office Version
  1. 365
Platform
  1. Windows
Did you try what Joe or I suggested?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,930
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Did you try what Joe or I suggested?
I thought maybe our posts when invisible or something...
 

JonesyUK

Board Regular
Joined
Oct 11, 2005
Messages
183
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNO
15
2405240108558022
358524010855811220156
41085240108558022009
Data
Cell Formulas
RangeFormula
A2:A4A2=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....??
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,657
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
N.B. KP117's suggestion requires Array Enter (CSE) for versions prior to 365.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,660
Messages
5,637,626
Members
416,977
Latest member
kdoederlein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top