1
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
Posted 2015-11-06T15:43:16.540
Reputation: 153
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Θ
Posted 2015-11-06T15:43:16.540
Reputation: 1 157
Can you tell in advance type of values (integer, natural, real numbers), and their range (min - max)? Are non empty cells contiguous? – SΛLVΘ – 2015-11-06T17:57:49.903
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 andINDEXto 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 – 2015-11-06T18:09:14.870the column will only have numbers under 50. – Spongebob Squarepants – 2015-11-06T18:38:31.970