2

I have a column that'll have dynamic filled in fields but will be empty for the most part. I want to find the difference of last two values found in a column but nothing I've tried seems to work.

Spongebob Squarepants
  • 183
  • Can you tell in advance type of values (integer, natural, real numbers), and their range (min - max)? Are non empty cells contiguous? – SΛLVΘ Nov 06 '15 at 17:57
  • Assuming the column has numbers, the standard way to find the location of the last entry in Excel is =MATCH(9.99999999999999E+307,A:A,1). Once you have that, you can use a similar formula to find the location of the second to last entry, and then use those and INDEX to get the values at those locations. Those are Excel though, I don't know if they're available or work the same way in Google Sheets, but maybe it's a place to start. And excellent correct use of penultimate. :-) – blm Nov 06 '15 at 18:09
  • the column will only have numbers under 50. – Spongebob Squarepants Nov 06 '15 at 18:38

2 Answers2

2

This will get the value of the second cell up from the bottom:

=INDIRECT("A"&MAX(ARRAYFORMULA(row(A:A)*--(A:A<>"")-1)))
ZygD
  • 2,497
paulo negrini
  • 21
1

Not beautiful, but it works:

=INDEX(FILTER(A:A;NOT(ISBLANK(A:A)));ROWS(FILTER(A:A;NOT(ISBLANK(A:A)))))-INDEX(FILTER(A:A;NOT(ISBLANK(A:A)));ROWS(FILTER(A:A;NOT(ISBLANK(A:A))))-1)

Assuming you have values in column A, filter out empty cells, return squeezed array last value by counting rows, return squeezed array penultimate value by counting rows and subtracting 1.

SΛLVΘ
  • 1,405