Excel 2003 Formulae Increments

  si_gone 19:44 05 Nov 2008
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 2008

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 2008

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 2008

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 2008

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 2008

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.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

What went wrong at the Designs of the Year 2017

iPhone X news: Release date, price, new features & specs

Comment utiliser Live Photos ?