0

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.

Luuk
  • 12,245
  • 5
  • 22
  • 33
George Cireş
  • 9
  • 2
  • The function `=filter(E22:E52,E22:E52<>0)` seems to find all values unequal to `0`. You should be able to combine that with this Question/Answer: [Get the last non-empty cell in a column in Google Sheets](https://stackoverflow.com/questions/8116043/get-the-last-non-empty-cell-in-a-column-in-google-sheets), which finds empty cells, and not cells with a value not equal to zero. – Luuk May 29 '23 at 17:04
  • No matter what i put in it gives me the same Formula parse error. for context I'm trying to make a spreadsheet for my paycheck budget so maybe there s something to do with the currency? – George Cireş May 29 '23 at 21:16

3 Answers3

2

Variables

r     Source Range
n     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))))

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!}
  • 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)))

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)))
Blind Spots
  • 377
  • 2
  • 9
1

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.

z''
  • 4,527
  • 2
  • 3
  • 12
  • When I use your function in D2, it produces "test" (@blind-spots function is in C2), see: https://i.stack.imgur.com/i3Piy.png , and some explanation is missing. – Luuk May 30 '23 at 13:57
  • There are a lot of variations of this formula that really depend on how OP's data is structured, are there blank values? Text values? How many sequences of zeros may there be? Etc... Without this information we can only guess. I added a new solution anyway with a brief explanation. – z'' May 30 '23 at 16:54
  • thanks I hate the answer that only say "You can try...." without explanation – Luuk May 30 '23 at 16:55
  • That's understandable. The reason I don't usually explain my formulas is because the majority of the times writing out the explanation takes more time than coming up with the formula. And when that's not the case, the formula is so simple that doesn't even need an explanation. So unless the OP explicitly asks for it I don't usually explain it. – z'' May 30 '23 at 17:02
  • But there might be other readers, who find this question tomorrow, or in a week, or even next year.... And then the explanation is missing – Luuk May 30 '23 at 17:07
0

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 ,

marikamitsos
  • 10,264
  • 20
  • 26