Excel 2003 Formulae Increments

  si_gone 19:44 05 Nov 08
Locked

In Excel 2003 I am working across a row. First cell contains =SUM($BM74:$EZ74). I need the next cells to read =SUM($BM75:$EZ75), =SUM($BM76:$EZ76) and so on. I don't seem to be able to do that. Is it possible?

  VoG II 20:13 05 Nov 08

Say you are starting in column C then enter

=SUM(INDIRECT("$BM"&COLUMN()+71&":$EZ"&COLUMN()+71))

and drag the formula across.

The 71 is obtained by 74 - start column number.

Please note that this is an 'expensive formula' because both INDIRECT and COLUMN are volatile functions.

  si_gone 20:47 05 Nov 08

I am starting in a cell merged across columns BY and BZ. The next cell to the right is a merge of columns CA and CB. Is this going to cause me problems? Also, what is the significance of an 'expensive formula' and a 'volatile function'?

  VoG II 20:53 05 Nov 08

Don't use merged cells - they are the work of the devil. Unmerge them then select the cells, Format > Cells > Alignment tab and for horizontal alignment select Center across selection.

Expensive formulas are those that consume significant processing effort and therefore can cause calculation to be slow.

For volatile functions see click here

  si_gone 21:17 05 Nov 08

Right. I ave un-merged all the cells and centered across the selecions. However, what do you mean in your last response by '71 is obtained by 74 - start column number'? Is that implying that the 71 is the 74 minus C (3 - the third column)? If so, then column BY would be no.77? I have I got the wrong end of the stick?

  VoG II 21:58 05 Nov 08

Try

=SUM(INDIRECT("$BM"&COLUMN()-3&":$EZ"&COLUMN()-3))

and drag to the right.

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation technologies coming to Siggraph 2017

iPad Pro 12.9 vs Surface Pro 5