Strange update (Excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

Looking at your formulas, the formula for cell B7 is:

=SUM(B1:B4)-(SUM(B4:B6)

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:

=SUM(B1:B3)-(SUM(B4:B6)

HTH

Reply | Quote

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

Reply | Quote

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 .

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Try:

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

Aladin

Reply | Quote

It’s caused by “Extend list formats and formulas” on the Edit tab if this feature is checked.

Aladin

Reply | Quote