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