The attached has two possible solutions. In Sheet1 I "shadowed" your column B formula with one that does what you want every 20 rows. Adapt the numbers to suite the 55 rows. The formula in C2 and copied down is
Formula:
=A2/SUM(INDEX($A$2:$A$112,1+(CEILING(ROWS($1:1)/20,1)-1)*20):INDEX($A$2:$A$112,20+(CEILING(ROWS($1:1)/20,1)-1)*20))*40
It creates a kind of "traveling" range as you copy down and changes the 20 (55) row range reference every 20 rows. It sums them in each row, divides column A by that and then multiplies 40.
In Sheet2 an alternative would be to place the sums every 20 (in life 55) to make a helper column. This saves Excel having to use the SUM function each row. With 45000 rows I suspect you will want all the performance edge you can get. In Sheet2 the helper (column C) starting in C21 is
Formula:
=SUM(A2:A21)
Be sure to maintain the relative cell addressing. Then copy C2:C21 and paste below in C22. Then copy and paste those 40 rows and repeat. If you are not familiar with doubling effects don't be intimidated. This should take no more than about 10-11 re-doublings of the 55 rows to complete the 45000. Then the formula in B2 would be
Formula:
=A2/INDEX($C$2:$C$112,20+(CEILING(ROWS($1:1)/20,1)-1)*20)*40
for 20 rows. The advantage of this is that the formula references the sum rather than having to recalculate it every row.
Let us know what you think.
Bookmarks