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.

jmrype The MOD function is REST in Norwegian, and SUM is SUMMER.

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

HansVogelaar, thank you for your patience with me.I think you mixed up the function with both english and norwegian, you too? 😉=HVIS(MOD(RAD();5)=0;AVRUND(STØRST(SUMMER(K16:K19)-M19;0);5);"")If I change the 'MOD' to 'REST' and change from '=' to '>' the calculate get accactley. With the '=' I got it wrong. Nothing shows in the cell. But I can not find a way to get it automatic filled in to the other cells down under. I have tried to mark all cells in one collone, and ctrl + D as sombody told me to. I have also tested to pull the cross down, because the formula should be able to calculate then, like it does in the test table.Pic under show the formuls give the accurate number, but it also give al lot of other cels between filled with (random) numbers. =HVIS(REST(RAD();5)>0;AVRUND(STØRST(SUMMER(K16:K19)-M19;0);5);"")

jmrype Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? That way I could experiment, and the formulas would automatically be translated from Norwegian to English and back.

Formulas automatic every fifht row | Microsoft Community Hub

11 Replies

PeterBartholomew1
Bronze Contributor
Feb 18, 2024
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
  Copper Contributor
  Mar 09, 2024
  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.
  - SergeiBaklan
    Diamond Contributor
    Mar 09, 2024
    jmrype
    
    Attached file repeats PeterBartholomew1 formula
    
    Try to open it in your environment to check if formula works.
    Formulas automatic every fifht row.xlsx12 KB
HansVogelaar
MVP
Feb 18, 2024
jmrype

The MOD function is REST in Norwegian, and SUM is SUMMER.
- jmrype
  Copper Contributor
  Feb 18, 2024
  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.
  - HansVogelaar
    MVP
    Feb 18, 2024
    jmrype
    
    Does this do what you want?
    
    =HVIS(MOD(RAD();5)=0;AVRUND(STØRST(SUMMER(K16:K19)-M19;0);5);"")

Forum Discussion

Formulas automatic every fifht row

11 Replies

Resources