# 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.

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 ?