Results 1 to 8 of 8
Thread: Strange update (Excel 2000)

20020620, 14:23 #1
 Join Date
 Aug 2001
 Location
 Cape Town, South Africa, South Africa
 Posts
 399
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20020620, 14:41 #2
 Join Date
 May 2002
 Location
 Midland, Michigan, USA
 Posts
 133
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20020620, 15:17 #3
 Join Date
 Aug 2001
 Location
 Cape Town, South Africa, South Africa
 Posts
 399
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20020620, 15:25 #4
 Join Date
 May 2002
 Location
 Midland, Michigan, USA
 Posts
 133
 Thanks
 0
 Thanked 0 Times in 0 Posts
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>.

20020620, 15:48 #5
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.Legare Coleman

20020621, 06:28 #6
 Join Date
 Aug 2001
 Location
 Cape Town, South Africa, South Africa
 Posts
 399
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20020621, 07:01 #7
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Strange update (Excel 2000)
Try:
=SUM(OFFSET($A$1,0,0,3,1))SUM(OFFSET($A$4,0,0,3,1))
AladinMicrosoft MVP  Excel

20020621, 07:04 #8
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Strange update (Excel 2000)
It's caused by "Extend list formats and formulas" on the Edit tab if this feature is checked.
AladinMicrosoft MVP  Excel