CODEWITHSUNDEEP
excel-formula
broccoli
broccoli

Reputation: 25

Get last 5 values ignoring blanks excel

So I have data consisting of the following: enter image description here

there are multiple more rows.

Im trying to retrieve the last 5 values in a row, for further use. I can do this with a simple INDEX(MATCH()) setup, however that doesn't ignore the blank cells, which I would like it to do. It does successfully detect the first nonblank cell and returns that, however when it's blank in the middle of the 5 most recent, it doesn't remove that.

something like this is what it does now:

enter image description here

however i want it to come back in this format:

enter image description here

and so on. A blank could occur at any position, and there may not always be 5 scores available to retrieve.

TIA

Upvotes: 0

Views: 954

Answers (2)

P.b
P.b

Reputation: 11438

You could use the following array-formula (entered with ctrl+shift+enter in older Excel versions):

=INDEX(1:1,AGGREGATE(14,6,COLUMN(A:G)/(A1:G1<>""),{5,4,3,2,1})) copied down.

Aggregate creates an array of the column numbers divided by 1 or 0 (TRUE or FALSE). Divided by 0 results in an error and gets ignored. It then takes the 5th largest to the largest column number without error and returns that as an array on the indexed row.

Where 1:1 represents the first row and A:G represents the first to last column used.

If you would want it on row 2 and column A:Z you'd have to amend it like this: =INDEX(2:2,AGGREGATE(14,6,COLUMN(A:Z)/(A2:Z2<>""),{5,4,3,2,1}))

Upvotes: 1

Ike
Ike

Reputation: 13014

Different approach - using the new Excel 365 formulas:

This will return the values of the last five non-empty cells of row 2

=LET(
    data,FILTER(B2:H2,B2:H2<>""),
    cntData,COUNT(data),
    matrix,SEQUENCE(1,MIN(cntData,5),IF(cntData>5,cntData-4,1)),
    INDEX(data,1,matrix)
   )
  • data returns all values except empty cells using the FILTER- formula
  • cntData holds the number of cells
  • using SEQUENCE a matrix is build that will return the column-indices to be returned. In case less then 5 values are available, the matrix returns 1 to cntData.
  • finally this "index-matrix" is used to return the values from the filtered data

This could be enhanced, by storing the number of cells to be returned within a named cell - and referencing this name in the formula. By that you could easily alter the number without altering the formula.

Upvotes: 0

Related Questions