Apr 02 2020 11:09 AM
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?
Apr 02 2020 01:42 PM
SUM-OFFSET sounds like an option for you. Do you have a sample workbook?
Apr 02 2020 02:04 PM
Hello, this is close to one of my recent Youtube post. Check it to see if it actually answers your question
Apr 02 2020 02:32 PM
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.
Apr 02 2020 02:39 PM
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.
Apr 03 2020 07:40 AM
Try this. I presumed the Goals scored = "Con" column. If it's the other column then you can change the criteria.
Apr 03 2020 08:43 AM
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
Thanks in advance
Apr 03 2020 09:02 AM
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}))),"")
Apr 03 2020 09:15 AM
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.
Apr 05 2020 02:01 PM
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?
Apr 05 2020 03:38 PM
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
Apr 05 2020 04:13 PM
It is not working for me.
Could you put the formula in the file uploaded in the previous comment?
Thanks
Apr 06 2020 01:29 AM - edited Apr 06 2020 01:51 AM
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)))