Forum Discussion
Offset
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.
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
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.
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?
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}))),"")
