# Thread: Strange update (Excel 2000)

1. ## Strange update (Excel 2000)

I found a very strange thing.

I have a list of 6 values with a formula of =Sum(a1:a3)-sum(a4:a6).All the value is nul.
Now what is strange if I have a values in cell a1 to a3 and put a value in cell a4 the function changes to =Sum(a1:a4)-sum(a4:a6).

Refer attached file

Are there a place to put this nice/ irritating feature off.

Thanks

Mario

2. ## Re: Strange update (Excel 2000)

Looking at your formulas, the formula for cell B7 is:
<pre>=SUM(B1:B4)-(SUM(B4:B6)</pre>

So, it's automatically subtracting the value of cell B4 from the equation as soon as it is added, yielding the same as the sum of B1:B3.
(4+5+5+45)-(45) = (4+5+5)

Perhaps you meant to use:
<pre>=SUM(B1:B3)-(SUM(B4:B6)</pre>

HTH

3. ## Re: Strange update (Excel 2000)

I need a fix to this auto change Excel does.
I have thought of either putting a hidden row in or making the formula constant
Any other ideas?

Thanks

Mario

4. ## Re: Strange update (Excel 2000)

I'm a little bit confused - perhaps you can clarify.
Which is the formula that you want to have in B7:
=SUM(B1:B3)-SUM(B4:B6)
OR
=SUM(B1:B4)-SUM(B4:B6)
And, given the data you supplied in your first post, what is the result that you expect?
I don't quite understand the auto change that you're talking about <img src=/S/confused.gif border=0 alt=confused width=15 height=20>.

5. ## Re: Strange update (Excel 2000)

That does not seem to be happening for me. If I enter a value in cell A4 on your sheet, the formula does not change. Can you give a more detailed description of what you are doing?

Also, check in Tools/Options on the Transition tab to make sure that "Transition navigation keys," "Transition formula evaluation," and "Transition formula entry" are not checked.

6. ## Re: Strange update (Excel 2000)

I have a list of values where I have 6 values underneath each other. The first 3 I would like to add and then deduct the sum of the last 3 values - formula Sum(A1:A3)-Sum(A4:A6). As soon as I enter value 1 to 3 and then enter a value in A4 the formula changes to Sum(A1:A4)-Sum(A4:A6). I've cleared the checks on "Transition formula evaluation," and "Transition formula entry" and saved the file.

Now I would like Excel to keep the formula as Sum(A1:A3)-Sum(A4:A6) and not change it

Thanks for all the input

Regards

Mario Smit

7. ## Re: Strange update (Excel 2000)

Try:

=SUM(OFFSET(\$A\$1,0,0,3,1))-SUM(OFFSET(\$A\$4,0,0,3,1))

8. ## Re: Strange update (Excel 2000)

It's caused by "Extend list formats and formulas" on the Edit tab if this feature is checked.