Offset

[Jrm92]

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? 

[#2]

@Jrm92 

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

[#3]

@Jrm92 

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

https://www.youtube.com/watch?v=v4yEpYhgwE4&t=61s

[#4]

@Jrm92 

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. 

[#5]

@Patrick2788 

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.

[#6]

@Jrm92 

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

[#7]

@Patrick2788 

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

[#8]

@Jrm92 

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}))),"")

[#9]

@Jrm92 

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. 

[#10]

@Patrick2788 

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?

[#11]

@Jrm92 

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

[#12]

@Peter Bartholomew 

It is not working for me.

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

Thanks

[#13]

@Jrm92 

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)))