# Offset

Occasional Contributor

# Offset

When there are different cells containing numerical data how do I get the data from the latest 5 cells?

I think it's offset but how to arrange this?

The value of the latest 5 cells should be summed up in one cell.

The cells from which to retrieve data are in the same row but with other data and text in between.

How to solve this?

12 Replies
Highlighted

# Re: Offset

SUM-OFFSET sounds like an option for you. Do you have a sample workbook?

Highlighted

# Re: Offset

Hello, this is close to one of my recent Youtube post. Check it to see if it actually answers your question

Highlighted

# Re: Offset

For the model as here

it could be

``=SUM(INDEX(2:2,1,AGGREGATE(14,6,1/ISNUMBER(2:2)*COLUMN(2:2),{5,4,3,2,1})))``

AGGREGATE returns positions of the latest non-texts in row #2, INDEX takes values from these position and finally we SUM them.

Highlighted

# Re: Offset

When I fill in the next round of scores the yellow cell should calculate the amount of goals scored in the latest five matches. This has to be the case till all matches are played.

Highlighted

# Re: Offset

Try this. I presumed the Goals scored = "Con" column.  If it's the other column then you can change the criteria.

Highlighted

# Re: Offset

Almost there, in the MATCH part of the formula there is MATCH: (20;6:6;1)-9

20 is the Look up value,  why is this the lookup value?

6:6 is the row

;1 is exact value

-9 =?

Can you explain this so I can adjust it in my own file

Highlighted

# Re: Offset

Just in case, that is adjusting of previous variant to the sample

``=IFERROR(SUM(INDEX(\$G7:\$XFD7,1,AGGREGATE(14,6,1/(\$G\$4:\$XFD\$4="Con")/ISNUMBER(\$G7:\$XFD7)*(COLUMN(\$G\$4:\$XFD\$4)-COLUMN(\$F\$4)),{5,4,3,2,1}))),"")``
Highlighted

# Re: Offset

The MATCH finds the position of the last number. I'm matching 20 so MATCH doesn't return the position before the last number.  It will never find a 20 in that row so it returns the position of the last number.  The -9 ensures the array starts at instance 1 of where we want to begin summing.

Highlighted

# Re: Offset

I can't get the appropriate distances in the match part of the formula.

I adjusted the exampke can you take a look at it?

Highlighted

# Re: Offset

Taking the opportunity to play with new functions

= LET( played, MAX( IF( ISNUMBER(scores), round ) ),
idx, 2 * ( played-{4,3,2,1,0} ),
Sco, INDEX( scores, idx-1 ),
SUM(Sco) )

and

= LET( played, MAX( IF( ISNUMBER(scores), round ) ),
idx, 2 * ( played-{4,3,2,1,0} ),
Con, INDEX( scores, idx ),
SUM(Con) )

are the sum of scores for and against

Highlighted

# Re: Offset

It is not working for me.

Could you put the formula in the file uploaded in the previous comment?

Thanks

Highlighted

# Re: Offset

The LET function has only recently been released on the insider's channel for beta testing.  Without it, there is a catch in that the INDEX function prevents spilling so the formula needs coercion to work.

played: = MAX( IF( ISNUMBER(scores), round ) )

idx: = 2 * ( played - {4,3,2,1,0} )

Sco: = INDEX( scores, idx-1 )

Con: = INDEX( scores, idx )

= SUM(N(IF(1,Sco)))

= SUM(N(IF(1,Con)))