Forum Discussion
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
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
It is not working for me.
Could you put the formula in the file uploaded in the previous comment?
Thanks
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)))
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.
Hello, this is close to one of my recent Youtube post. Check it to see if it actually answers your question
SUM-OFFSET sounds like an option for you. Do you have a sample workbook?
Yes I made an example it is about a sportsteam.
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.
Try this. I presumed the Goals scored = "Con" column. If it's the other column then you can change the criteria.
