I want to use Sparkline for a spreadsheet to show a trend of the last 5 soccer matches, where A and B are the goals, and C are the resulting points.

In column C, the points are only generated if values are entered for the goals and goals conceded, i.e. the columns are not empty.

As you see, in row 3, column c is empty.

What I basically try to achieve, is to create a list where the last 5 entries which are not empty / null, are listed:

Is used this formula, but it somehow does not work

=query(J15:J114,"select * offset "&count(J15:J114)-5)

shorturl.at/gHPY9 (example result picture)

Tried to find a solution myself, but am stuck.

Best, Feal

CodePudding user response：

Use query() with a where clause, like this:

=query( 
  J15:J114, 
  "where J is not null 
   offset " & max(0, count(J15:J114) - 5), 
  0 
)

CodePudding user response：

Here is an example of how you could use the QUERY function in Google Sheets to create a list of the last 5 non-empty entries in a column:

=QUERY(J15:J114, "SELECT * WHERE J15:J114 IS NOT NULL ORDER BY ROW() DESC LIMIT 5", 0)

In this formula, the QUERY function takes three arguments:

1. The range of cells that you want to query (J15:J114 in this example)
2. The query that you want to run on the range of cells (SELECT * WHERE J15:J114 IS NOT NULL ORDER BY ROW() DESC LIMIT 5 in this example)
3. A flag indicating whether or not the first row of the range should be treated as column labels (0 in this example, which means that the first row will not be treated as column labels)

The query that we use in this formula filters the range of cells to only include rows where the value in the J column is not NULL. It then sorts the rows in descending order based on their row number, and limits the result to the last 5 rows. This will give us a list of the last 5 non-empty entries in the J column.

I hope this helps! Let me know if you have any questions.