Help with Sum Function Issue

  • Thread starter msnews.microsoft.com
  • Start date
M

msnews.microsoft.com

I have excel 2003.

If I add a row of numbers 1,2,3 I get 6 which is good.
I use a sum(x:y) to get it.
If I insert a row under the 3, the formula does not change.
If I add a number to that new row, the forumla will change to reflect adding
the number.

It is hit and miss when it does it, how do I understand this function and
possibly turn it off. In some cases I do not want the formula to update
based on empty rows having numbers added.

Thank you.
 
S

Sandy Mann

I don't have Excel 2003 and with your chosen nom de plume I find it
impossible to search to see if your query has been answered in another
thread - so being as there seem to be no other replies, I assume that Excel
is extending the formula for you. I think that there is an option to turn
it off in Tools > Options > Calculation but I could be wrong.

In place of SUM(A1:A3) you could use:

=SUM(A1:OFFSET(A1,2,0))

However, OFFSET is a volatile function and so will calculate at every
calculation not just when the dependants are changed so if you have enough
of them there could be a performance penalty

--
HTH

Sandy
[email protected]
[email protected] with @tiscali.co.uk
 
R

Ragdyer

My XL2k has the option.

<Tools> <Options> <Edit> tab,

And *UNCHECK*
"Extend List Formats And Formulas".

In XL03 the wording is a little different:
"Extend Data Range Formats & Formulas"
 
Top