Forum Discussion

Jacob_Afelskie's avatar
Jacob_Afelskie
Copper Contributor
May 21, 2020

Need help with an OFFSET formula applying to a block of rows

Hello,

 

I'm attempting to apply a formula to 4 cells in a column, then have it apply the same formula to 4 more cells nth rows below repeating. I've attempted to use OFFSET for this method, but I'm currently lost.

 

Thank you

excel
Formulas and Functions

7 Replies

    • Jacob_Afelskie's avatar
      Jacob_Afelskie
      Copper Contributor
      May 21, 2020

      bhushan_z In the Transformations sheet I am attempting to move the first 4 values from column L to column M, then 14 rows below, those 4 values, etc. They are the Lysate values from sheet Input, column M. I have not included the formula I wish to apply to those values yet, I just want them organized in a column so that I may call them readily. Thank you

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        May 21, 2020

        Jacob_Afelskie 

        Not sure I understood your logic correctly

        to move the first 4 values from column L to column M,

        to move or to copy?

        then 14 rows below, those 4 values, etc.

        4 values in column L starting from next 14 copy to column M next position to previous 4?

         

        Anyway, formula could be

        =INDEX(L:L,INT((ROW()-ROW($M$2)-1)/4)*14+MOD(ROW()-ROW($M$2)-1,4)+ROW($M$2)+1)

         

Resources