Excel 2007 Automatically changing formulas?! 10

[ColourfulFigsnDiags]

Ok so I have a bunch of numbers in cells A1, B2 C1 and D1. In cell E1 I have a formula which sums cells A1, B1 and C1.

I fill that formula down a pile of rows, and look at cell (for example) E7 and sure enough the formula still says =sum(A7:C7)

But, for some odd reason, when I enter the numbers in cells A7, B7, C7, and D7, the formula changes, automatically, to =Sum(A77) !!!! I do NOT want cell D7 in the sum.

Is this a stupid autocorrect thing? If so it is bloody dangerous and I want to turn it off.

Any thoughts?

Read the Eng-Tips Site Policies at FAQ731-376

 

[electricpete]

It's not an excel 2007 thing - I just recreated the same behavior in excel 2000:

Type =sum(A1:C1) in E1.
Then type 2 4 6 8 in cells A1, B1, C1, D1
E1 displays the value 20
E1 formula reads sum(A1:C1)

I agree. It is bloody scary.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Sorry, I was distracted. once correction in bold:

Type =sum(A1:C1) in E1.
Then type 2 4 6 8 in cells A1, B1, C1, D1
E1 displays the value 20
E1 formula reads sum(A1:D1)

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[GregLocock]

Yup 2003 as well.

That is very bad.

Cheers

Greg Locock

SIGlease see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[ColourfulFigsnDiags]

I've found out how to stop Excel doing it.

In Excel 2007 click the file button ... go to "excel options" >> go to the "Advanced" tab >> uncheck "Extend data range formats and formulas"

This is a very scary "feature". I recommend you advise your work collegues about it and turn it off. It could cause all sorts of drama!

Read the Eng-Tips Site Policies at FAQ731-376

 

[IDS]

I agree that having formulas automatically change when you enter data in a blank cell is just crazy.

In fact the whole feature, including the auto-format thing, is more trouble than it is worth 9 times out of 10; I don't know why I didn't switch it off before.

Thanks for pointing it out colourfulfigsetc

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[MintJulep]

It's on

Tools | Options | Edit

in 2003.

 

[jhardy1]

A star each for ColourfulFigsnDiags & MintJulep for highlighting the problem (which i had encountered before), and more importantly - how to fix it!

MOST of the time, the auto-range "feature" works pretty well to intuitively determine the appropriate range, but SOMETIMES it is not only frustrating, but downright dangerous!

 

[BAretired]

I checked it out in Excel 2003 and got 12 for the sum (as it should be). I assumed the feature must have been turned off, so I went to Tools|Options|Edit and found that the feature "extend data range formats and formulas" is checked. When I unchecked it, the total remains 12.

So I am not finding the same problem as the rest of you. Seems like there is more to the problem than we have discovered to date.

BA

 

[ColourfulFigsnDiags]

Um I think you might have missed the point.

You will get the right sum but the formula will automatically change itself

Do this
[tt]
[tab]A|[tab]B|[tab]C|[tab]D|[tab]E
---------------------------------------
1| 4|[tab]6|[tab]5|[tab] |[tab]=sum(A1:C1)
---------------------------------------
2| |[tab] |[tab] |[tab] |[tab]
---------------------------------------
3| |[tab] |[tab] |[tab] |[tab]
[/tt]
Note that cell D1 is empty. Cell E1 should evaluate (correctly) to 15, regardless of whether "extend data range formats and formulas" is checked or not. This is not the problem.

... now enter a number into D1 and dont change anything elsei.e.
[tt]
[tab]A|[tab]B|[tab]C|[tab]D|[tab]E
---------------------------------------
1| 4|[tab]6|[tab]5|[tab]8|[tab]=sum(A1:D1)
---------------------------------------
2| |[tab] |[tab] |[tab] |[tab]
---------------------------------------
3| |[tab] |[tab] |[tab] |[tab]
[/tt]
When you enter the number then excel Magically changes cell E1 to "=sum(A11)" NOT "=sum(A1:C1)" as you originally wanted, and therefore E1 evaluates to 23! Excel will do this if you have "extend data range formats and formulas" checked, which it is by default.

Check again and see if it does this.

Read the Eng-Tips Site Policies at FAQ731-376

 

[BAretired]

colorful,

You are correct. Excel 2003 did just what you indicated. What a silly feature in the program. Why would they do that? Perhaps it is time to let Microsoft know what we think of this idiotic feature.

BA

 

[IRstuff]

It makes sense for those that are constantly increasing the size of their data tables.

I recall, distinctly, talking to Bill about this, where I had to copy the last row, PasteInsert above it, and then add the new data over the original last row.

Bill suggested that they could add a feature where all you needed to do is insert the row, type in the new data, and the table would automatically extend itself.

TTFN

FAQ731-376

 

[IDS]

"It makes sense for those that are constantly increasing the size of their data tables."

Perhaps (although I'm not convinced), but if this is an intended feature it should be clearly documented, it certainly shouldn't be the default setting, and when you switch it on you should get a warning in big red letters.

This is what the Excel 2007 help says about this "feature":

”Extend data range formats and formulas Select to automatically format new items added to the end of a list to match the format of the rest of the list. Formulas that are repeated in every row are also copied. To be extended, formats and formulas must appear in at least three of the five last rows preceding the new row.”

So the idea is it copies formats and formulas. It doesn't say a word about changing existing formulas.

I think it's a bug.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IRstuff]

But it is clearly documented, as you so readily demonstrated. Not only is there a help bubble on the check box in Options|Edit, but there are entries in the main help files.

Philosophically, I'm not convinced that a warning is in order. You have a list of numbers to be summed, and you tack a number between the list and sum. I would argue that it would be quite reasonable to expect that you intend to increase the sum to include this new number. Otherwise, why would you not provide some separation? From a readability perspective, in CFD's example, knowing that column E contains a sum, why would you not expect column D to be included?

TTFN

FAQ731-376

 

[electricpete]

You and Bill have a lot of convesations, do you?

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IDS]

"But it is clearly documented, as you so readily demonstrated."


Did you read what it says?

It says it copies formats and copies formulas if they are repeated in every row. I don't have a problem with that, provided you can turn it off. What I do have a problem with is editing previously entered formulas without warning, and having this as the default behaviour. The help gives no hint that that is how it works.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[ColourfulFigsnDiags]

You have a list of numbers to be summed, and you tack a number between the list and sum. I would argue that it would be quite reasonable to expect that you intend to increase the sum to include this new number. Otherwise, why would you not provide some separation?

No, I'm sorry it is not at all reasonable. If I wanted to sum all those cells I would have done so in the first place, I don't want excel to be second guessing my intentions.

FWIW The reason I wanted to sum the first 3 cells and not the 4th one is fairly reasonable and typical in a data entry situation.

I had some mass flowrate data that had been extracted from a few graphs, Gas mass flowrate, Water mass flowrate and oil mass flowrate. I also had a printed list of the total mass flowarate.

I wanted to check that the total mass flowrate was equal to the sum of the other 3. I had to manually enter that data in, so it is clearly easiest to do that with the entered data all in adjacent columns. i.e. GasMF in Col A, Water MF in Col B, OilMF in col C and totalMF in col D. Col E was a sum of columns A, B and C. Column F was a warning statement to let me know if the difference was greater than a certain amount (which would indicate a possible error in reading the graphs).

This "feature" is incredibly dangerous when you think about the implications it could have on a previously verified calculation sheet. It is quite reasonable to expect that previously verified formulas should not magically change themselves.

Read the Eng-Tips Site Policies at FAQ731-376

 

[BAretired]

ColourfulFigsnDiags,

I agree with you completely. It is a very bad feature of the program. Now that we know about it, we can avoid it but what about the millions of folks who don't? Should we be doing something about it?

BA

 

[jhardy1]

"Now that we know about it, we can avoid it but what about the millions of folks who don't? Should we be doing something about it? "

I think we should get IRStuff to have a talk to Bill about this - once it is clearly explained how dangerous this can be, I am sure Bill will have a fix issued immediately!

 

[djack77494]

I wonder if this behaviour could be avoided if absolute addressing were used (i.e. use $A$1:$C$1)? My feelings on this "feature" are mixed, but I certainly can see occassions when it would be desirable.