Find max and last value from a googlesheet query skipping x rows-CodePudding

I have a data set in google sheets, for each week of data I have 3 rows. I wish to query the data in every second row to calculate the max value and the last value.

For instance:

ROW | DATA

1 | 800

2 | Text

3 | 500

4 | More text

5 | 600

6 | Blah

7 | 700

8 | Blah

For Max value I have the following which will return 800 MAX(FILTER(QUERY(A1:A,"Select * skipping 2"), QUERY(A1:A,"Select * skipping 2") <> 0))

How do I change it up to return the last value? Which should return 700

CodePudding user response：

try:

=LOOKUP(2^99,FILTER(A:A,A:A<>0))

CodePudding user response：

@rockinfreakshow answer will successfully find the last number.

To filter a range by n amount of rows, you can use:

=FILTER(A:A,MOD(ROW(A:A),n)=1)

Change n with your desired value, and 1 with the number of row you want to get. 1 for the first, 2 for the second, but 0 if you want the nth one.

To find the last one, even if it's a text or number, you can use SORTN and SEQUENCE:

=SORTN(FILTER(A:A,MOD(ROW(A:A),n)=1,A:A<>""),1,1, SEQUENCE(COUNTA(FILTER(A:A,MOD(ROW(A:A),n)=1,A:A<>""))),0)

It orders the elements in reverse order and only chooses the first one

Remember to change n with the number of rows and =1 with the number of row you want to choose