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?
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).
