need a quick way to enter a formula—that changes every 55 rows—into 45K rows

[LCamille]

I'm writing a formula that will be repeated down column B of a table. The formula sums 55 values in column A of data, and finds the percentage of that sum the current parallel cell in column A represents.

There are 45,000 rows in the table.

The formulas in column B need to sum the first 55 cells of column A, and then starting in cell B56, the formula for the next 55 rows needs to sum cells A56 through A111, and then update to sum the next 55 values in the column when it reaches cell B112, and continue to update to reflect the sum of the next 55 rows until the last row.

Below are the first three formulae, from cells B2, B3, and B4:
=A2/(SUM($A$2:$A$56))
=A3/(SUM($A$2:$A$56))
=A4/(SUM($A$2:$A$56))

Below is the formula in cell B56:
=A57/(SUM($A$57:$A$111))

I'd like to Use Edit > Fill > Down to enter all the formulae into the 45,000 rows of data, but because the sum function has to be identical for 55 rows, and then be updated to a new range for the next 55 rows, I can't.

Is there a way to enter the correct, changing formula in all the cells of column B without copying and pasting 55 cells at a time?

Thanks for any help you can offer!

- LCamille

[FlameRetired]

Probably yes. But if you could upload a sample Excel file (with desensitized "dummy" data) with say 100 rows or so it would be helpful.

Edit Sorry. My bad.

If you are not familiar with how to do that:

To attach a file to your post,
click “Go Advanced” (next to Post Quick Reply),
scroll down until you see “Manage Attachments”,
click that and select “Add Files” (top right corner).
click on “Browse”
select your file(s)
click “Open” click “Upload” click “Done” (bottom right)
click “Submit Reply”
Once the upload is completed the file name will appear at the bottom of your reply.

[LCamille]

Added an example file. Thanks!

[FlameRetired]

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

[FlameRetired]

Another way to do the helper column instead of the copy / paste would be with this formula in the helper column.

Please Login or Register  to view this content.

I don't know why I didn't think of it before. Sorry.

[LCamille]

Column C worked beautifully! Didn't take 2 minutes, so I'd done it before I saw your update.

But column B gives me a #Div by 0! error as soon as I drag the formula past the first 55 rows. I'm not familiar with either index or ceiling, but I'll research both of those to see what I've done wrong. Here's how I modified your formula in order to fit the top cell of my actual spreadsheet:

=AF2/INDEX($AH$2:$AH$44936,55+(CEILING(ROWS($1:1)/55,1)-1)*20)*40

Here's what it looks like in the first cell where it gives me a #Div by 0! error:

=AF57/INDEX($AH$2:$AH$44936,55+(CEILING(ROWS($1:56)/55,1)-1)*20)*40

Thanks again for your help!

- LCamille

[LCamille]

Ah, I see I needed to change the last 20 to a 55 as well, and then I get accurate calculations! Thank you so much. This was a great quick fix for what looked like a several hour copy-paste issue.

- LCamille