Sum formula with relative reference

[BenIrwin]

I am trying to create a simple formula that will add a group of cells in sections of 4 rows. I.E. cell E2 should be =sum(D2:D5). Cell E3 should be =sum(D6:D9). Cell E4 should be =sum(D10:D13) and so on. How to I make this formula so it can fill a series like that? Please know I am working with a sheet of over 30k entries.
Thank you,
Ben

 

[Aladin Akyurek]

I am trying to create a simple formula that will add a group of cells in sections of 4 rows. I.E. cell E2 should be =sum(D2:D5). Cell E3 should be =sum(D6:D9). Cell E4 should be =sum(D10:D13) and so on. How to I make this formula so it can fill a series like that? Please know I am working with a sheet of over 30k entries.
Thank you,
Ben

E2, copied down:

=SUM(INDEX(D$2:D$1000,(ROWS(E$2:E2)-1)*4+1):INDEX(D$2:D$1000,(ROWS(E$2:E2))*4))

 

[Macropod]

Hi Ben,

Try:
=SUM(OFFSET($D$1,(ROW()-2)*3+1,0,3))
Put this in E2 and copy down as far as needed.

Cheers

 

[Aladin Akyurek]

Hi Ben,

Try:
=SUM(OFFSET($D$1,(ROW()-2)*3+1,0,3))
Put this in E2 and copy down as far as needed.

Cheers

Not robust... Try to insert some rows before the data/formula row.

 

[BenIrwin]

Aladin
That worked great!
Any chance you can explain to me what the heck you did. If not can you give me a keyword that I can search the forum to find out more about it?

 

[Macropod]

Aladin,

Apart from an error that had my formula looking at only 3 rows at a time instead of 4, it works perfectly well for the problem posed by the OP. FWIW, the formula should have been:
=SUM(OFFSET($D$1,(ROW()-2)*4+1,0,4))

Cheers

 

[pgc01]

Hi Macropod

You are right, your formula does, in fact, work perfectly for the problem posed by OP. Aladin's comment means that you can improve it. In this case in 2 ways: make it robust and non-volatile.

- It's not robust. This means that it's dependent on the specific position of the data.
Ex.: Suppose you want to add some info before the table. If you select row 1 and insert some rows to write that information, your formula will no longer work, because it was dependant on the table starting in row 2. Or if you decide to have the summary elsewhere and select the formulas in E2:E7K and move it to start in, for ex., F5. The formulas will stop working for the same reason. If you try Aladin's formula you find that it's indiferent to you moving the data around.

- It uses volatile functions. As you know some formulas are always recalculated every time the worksheet calculates. Offset() and Row() are 2 of them as are Now() or Rand(). This means that if, for ex., you have in the sheet a formula like =H1+1, each time you change H1, the worksheet calculates, and your 7K+ formulas will also recalculate although they may have nothing to do with it.

Best regards
PGC

 

[Macropod]

Hi PGC,

I question whether making the formula more complex than it needs to be is an improvement, especially when it suffers the same kind of robustness issues mine is claimed to suffer from. Aladin's formula stops working once data row 998 is passed - the OP has "over 30k entries". So much for robustness! My version works for as many rows as the sheet has data.

As for the volatility issue, if you time Excel's recalculation of both solutions, I think you'll find there's little in it.

Cheers