I cant get the last number that is not 0 from a selected area of columns

I can't get the last number that is not 0 from a selected area of columns.

I can't get the last number that is not =0 from E22:E52.

The columns are currency typed are both positive and negative numbers.

Variables

r &nbsp; 🠚 &nbsp; Source Range<br>
n &nbsp; 🠚 &nbsp; ISNUMBER() <> 0

Formula

• The LET function enables storing calculations in variables for later re-use. Using it is optional, but makes the formula shorter and perhaps easier to follow.
• The FILTER function is used to return an array of numbers n not equal to zero from the range r
• INDEX returns the last row in n which is calculated from a COUNT of all the rows in n.

Formula using LET

=LET(r,E22:E52,
     n,FILTER(r,ISNUMBER(r)*(r<>0)),
     INDEX(n,COUNT(n)))

Formula without LET

=INDEX(FILTER(E22:E52,ISNUMBER(E22:E52)*(E22:E52<>0)),
   COUNT(FILTER(E22:E52,ISNUMBER(E22:E52)*(E22:E52<>0))))

[<img src="https://i.stack.imgur.com/bztIb.png" width="350" />](https://i.stack.imgur.com/bztIb.png "click to enlarge")

String Filtering

The FILTER function in the formulas above won't return strings because one of its conditions uses ISNUMBER.

If Data Includes 'Number Strings'

To accept strings that match number formats, A18 in my example images, those strings must first be coerced to numbers.

• This can be done to all values in the range r inside an ARRAYFORMULA with the VALUE function, or using any math operation that would 'not' change the value of a number. For example,
  • VALUE(r), r+0, r×1, r÷1, r×(½+½), etc.
• The resulting array will be populated by numbers and #VALUE! errors (values that could not be coerced to numbers)
• The potential errors are not a problem for the FILTER function, which will return FALSE for any condition where the tested value resolves to an error, except if the condition is explicitly looking for an error. For example,
  • FILTER({0;1;1/0;2},{0;1;1/0;2}<>0)={1;2}
  • FILTER({0;1;1/0;2},{0;1;1/0;2}=0)={0}
  • FILTER({0;1;1/0;2},NOT(ISERROR({0;1;1/0;2}))={0;1;2}
  • FILTER({0;1;1/0;2},ISERROR({0;1;1/0;2})={#DIV/0!}<br>
• the ISNUMBER check becomes unnecessary since all values are now numbers or errors.

=LET(r,ARRAYFORMULA(E22:E52*1), 
     n,FILTER(r,r<>0),INDEX(n,ROWS(n)))

# or

=LET(r,ARRAYFORMULA(VALUE(E22:E52)), 
     n,FILTER(r,r<>0),INDEX(n,COUNT(n)))

[<img src="https://i.stack.imgur.com/sWgkQ.png" width="350" />](https://i.stack.imgur.com/sWgkQ.png "click to enlarge")

If Data NEVER Includes Strings

If there are NEVER any values in your data range that are text strings then you can simplify the formula by removing the ISNUMBER check

=LET(r,E22:E52, n,FILTER(r,r<>0),
     INDEX(n,COUNT(n)))

# or

=INDEX(FILTER(E22:E52,E22:E52<>0),
   COUNT(FILTER(E22:E52,E22:E52<>0)))

You could also try:

=LET(r,E22:E52,INDEX(r,MATCH(,ROW(r)*(r=0))))

Or:

=CHOOSEROWS(FILTER(E22:E52,E22:E52),-1)

The first formula uses MATCH with an approximate lookup to find the relative position of the last row that doesn't respect the condition E22:E52=0 and then uses INDEX to return the corresponding value.

The second formula filters out all the non zero values in E22:E52 and then uses CHOOSEROWS(...,-1) to return the last one.

One of these will work (depending on your locale)

=INDEX(FILTER(E22:E52;E22:E52<>0);
 COUNT(FILTER(E22:E52;E22:E52<>0)))

OR

=INDEX(FILTER(E22:E52,E22:E52<>0),
 COUNT(FILTER(E22:E52,E22:E52<>0)))

Notice the ; turning to ,