Forum Discussion
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
- PeterBartholomew1Silver Contributor
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.
- jmrypeCopper ContributorHi, 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.- SergeiBaklanDiamond Contributor
Attached file repeats PeterBartholomew1 formula
Try to open it in your environment to check if formula works.
The MOD function is REST in Norwegian, and SUM is SUMMER.
- jmrypeCopper 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.
Does this do what you want?
=HVIS(MOD(RAD();5)=0;AVRUND(STØRST(SUMMER(K16:K19)-M19;0);5);"")