Forum Discussion

jmrype's avatar
jmrype
Copper Contributor
Feb 18, 2024

Formulas automatic every fifht row

I have a table, lets say I want to sumcell K18 and K19 in cell L19. There is over 600 rows and I want to copy this formula in every fifth row. How am I suppose to do this without manuell steps?

I have tried this formula =HVIS(MOD(RAD();5)=0;SUM(K18:K19);""), but I get wrong messages: "#NAME". 

I have the function for auto calculate on.

11 Replies

  • jmrype 

    I fear that how I would approach such a problem is unlikely to be of value to many!

    = LET(
        wrapped,  WRAPROWS(data, 4),
        blockSum, BYROW(wrapped, LAMBDA(x, SUM(x))),
        TOCOL(EXPAND(blockSum,,4,""))
      )

    The formula uses the array shaping functions to block the data in 4 s across the sheet and then sums the rows to return block subtotals.  To redistribute the values the subtotal list is padded with blanks before returning them to a column.

     

    • jmrype's avatar
      jmrype
      Copper Contributor
      Hi, thank you very much.
      I could not find any way to get this working. I got a message about "LET", it said
      'The first name must be a valid name'.
      I tried to translate the functions to norwegian, but with no help.
      Thanks anayway. I have to few skills about functions.
    • jmrype's avatar
      jmrype
      Copper Contributor

      HansVogelaar, than you very much.

      I was aware of that (different function languages) but so opsessed about trying both in norwegian and english and did't see it was mixed up, so thank you so much. I am a totally novice in this.

      I got i right now in the test table, but still struggeling with automatic copying to the 600 other rows.

      The attachements "Formula_01" is showing that the formula is calculating the right answar.

      The second picture "Formula_02" is showing one formula =HVIS(SUMMER(K16:K19)>M19;AVRUND(SUMMER(K16:K19)-M19;5)) that I want to copy exsactly the same (but every fifth row), but it does not function. I don't understand how to combine the two formulas to get it right. 




       

      • jmrype 

        Does this do what you want?

         

        =HVIS(MOD(RAD();5)=0;AVRUND(STØRST(SUMMER(K16:K19)-M19;0);5);"")

Resources