1

I need to find the last numerical value in a column. I was using this formula to get the last value in column G, but I made some changes and it no longer works: =INDEX(G:G, COUNTA(G:G), 1). My column now looks like this:

645
2345
4674.2345
123.1
"-"
"-"
"-"

...and the formula returns "-". I want it to return 123.1. How can I do this?

drobot
  • 23
  • 5
  • Are there ever any numbers after the "-"? i.e. are the "-" always at the end of the data? – MTwem Mar 14 '21 at 22:48
  • The cell is a hyphen, and that's it. I didn't know how to put just a dash in the post without doing some formatting thing... (The cell is literally - ) – drobot Mar 16 '21 at 01:48

2 Answers2

4

There are many ways to go about this. Here is one of them:

=QUERY(FILTER({G:G,ROW(G:G)},ISNUMBER(G:G)),"Select Col1 ORDER BY Col2 Desc LIMIT 1")

FILTER creates a virtual array of only numeric values in G in the first column and the row of those numeric values in the second column.

QUERY returns flips the order by row number and returns only the new top value from the first column (which winds up being your last numeric value in the original range).

However, if your numeric values start at G1, and if there are only numeric values up to where you start adding hyphens in cells, you could just alter your original formula like this:

=INDEX(G:G,COUNT(G:G))

This would work because COUNT only counts numeric values while COUNTA counts all non-null values (including errors BTW).

Erik Tyler
  • 9,079
  • 2
  • 7
  • 11
  • I ended up using the second function, with INDEX, since my column only had numeric values up until the hyphens. I did have to make it =INDEX(G:G, SUM(COUNT(G:G), 1), 1) for some reason, since it was getting the second to last numerical value instead of the last one. Thanks! – drobot Mar 16 '21 at 04:14
  • This says to me that you have a header in G1 (which would not be counted by COUNT). – Erik Tyler Mar 16 '21 at 17:00
  • Yes, I do, and that makes sense. Adding 1 to the count balances it out. – drobot Mar 17 '21 at 21:28
  • You could achieve the same effect (and have a formula that more accurately reflects what's going on) by just changing the ranges in my formula from G:G to G2:G. – Erik Tyler Mar 17 '21 at 22:29
0

Not to take anything away from the accepted answer, but I've been working on this a bit lately in relation to this for the never-ending last row discussion and thought I'd share some potential similar solutions. These ideas are inspired by a pattern of google sheet array questions that seem to be coming up more often. I am also intentionally using different ways to do the same thing just to give people some ideas (i.e. left and Regex).

Last Row that is...

  • Number: =max(filter(row(G:G),isnumber(G:G)))
  • Text: =max(filter(row(G:G),isText(G:G)))
  • An error: =max(filter(row(G:G),iserror(G:G)))
  • Under 0 : =max(filter(row(G:G),G:G<0))
  • Also exists in column D: =max(filter(row(G:G),ISNUMBER(match(G:G,D:D,0))))
  • Not Blank: =max(filter(row(A:A),NOT(ISBLANK(A:A))))
  • Starts with ab: =max(filter(row(G:G),left(G:G,2)="ab"))
  • Contains the character !: =max(filter(row(G:G),isnumber(Find("!",G:G))))
  • Starts with a number: =max(filter(row(G:G),REGEXMATCH(G:G,"^\d")))
  • Only contains letters: =max(filter(row(G:G),REGEXMATCH(G:G,"^[a-zA-Z]+$")
  • Last four digits are upper case: =Max(filter(row(G:G),REGEXMATCH(G:G,"[A-Z]{4}$")))

To get the actual value (which I realize was the actual question), just wrap an index function around the Max function. So for this question, a solution could be :

=Index(G:G,max(filter(row(G:G),isnumber(G:G))))
pgSystemTester
  • 8,979
  • 2
  • 23
  • 49