Help with Sum Function Issue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

msnews.microsoft.com - 28 Dec 2005 21:58 GMT

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.

Reply to this Message

Sandy Mann - 29 Dec 2005 19:28 GMT

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

Signature

HTH

Sandy
sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

>I have excel 2003.
>
[quoted text clipped - 9 lines]
>
> Thank you.

Reply to this Message

Ragdyer - 29 Dec 2005 20:10 GMT

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"

Signature

HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

>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
[quoted text clipped - 24 lines]
>>
>> Thank you.

Reply to this Message