Forum Discussion

Jrm92's avatar
Jrm92
Copper Contributor
Apr 02, 2020

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 ...
excel
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Apr 05, 2020

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

Jrm92's avatar
Jrm92
Copper Contributor
Apr 05, 2020

PeterBartholomew1 

 

It is not working for me.

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

 

Thanks

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Apr 06, 2020

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

     

Resources