×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel 2007 Automatically changing formulas?!
10

Excel 2007 Automatically changing formulas?!

Excel 2007 Automatically changing formulas?!

(OP)
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(A7:D7) !!!! 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: Eng-Tips.com Forum Policies  
 

RE: Excel 2007 Automatically changing formulas?!

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.

RE: Excel 2007 Automatically changing formulas?!

Sorry, I was distracted. once correction in bold:

Quote:


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.

RE: Excel 2007 Automatically changing formulas?!

Yup 2003 as well.

That is very bad.

Cheers

Greg Locock

SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: Excel 2007 Automatically changing formulas?!

6
(OP)
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: Eng-Tips.com Forum Policies  
 

RE: Excel 2007 Automatically changing formulas?!

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/
 

RE: Excel 2007 Automatically changing formulas?!

3
It's on

Tools | Options | Edit

in 2003.

RE: Excel 2007 Automatically changing formulas?!

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!

RE: Excel 2007 Automatically changing formulas?!

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

RE: Excel 2007 Automatically changing formulas?!

(OP)
Um I think you might have missed the point.

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

Do this

    A|    B|    C|    D|    E
---------------------------------------
1|  4|    6|    5|     |    =sum(A1:C1)
---------------------------------------
2|   |     |     |     |    
---------------------------------------
3|   |     |     |     |    

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.

    A|    B|    C|    D|    E
---------------------------------------
1|  4|    6|    5|    8|    =sum(A1:D1)
---------------------------------------
2|   |     |     |     |    
---------------------------------------
3|   |     |     |     |    

When you enter the number then excel Magically changes cell E1 to "=sum(A1:D1)" 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. smile

Read the Eng-Tips Site Policies at FAQ731-376: Eng-Tips.com Forum Policies  
 

RE: Excel 2007 Automatically changing formulas?!

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

RE: Excel 2007 Automatically changing formulas?!

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: Eng-Tips.com Forum Policies

RE: Excel 2007 Automatically changing formulas?!

"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/
 

RE: Excel 2007 Automatically changing formulas?!

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: Eng-Tips.com Forum Policies

RE: Excel 2007 Automatically changing formulas?!

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

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

RE: Excel 2007 Automatically changing formulas?!

"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/
 

RE: Excel 2007 Automatically changing formulas?!

(OP)

Quote:

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: Eng-Tips.com Forum Policies  
 

RE: Excel 2007 Automatically changing formulas?!

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

RE: Excel 2007 Automatically changing formulas?!

"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! thumbsup2

RE: Excel 2007 Automatically changing formulas?!

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.

RE: Excel 2007 Automatically changing formulas?!

My excel only does this for horizontal sums - not vertical ones!

RE: Excel 2007 Automatically changing formulas?!

"My excel only does this for horizontal sums - not vertical ones!"

Interesting .... my copy of Excel 2003 (SP3) changes the formula on both horizontal sums and vertical sums if I enter a number into the blank row or column.

RE: Excel 2007 Automatically changing formulas?!

For my vertical sums, Excel puts one of those little triangles in the cell corner and a pulldown menu next to the cell that ASKS if I want the formula extended.  Both for 2003 and 2007 versions.

This is all academic for me because I use OpenOffice 99% of the time anyway.  But, interesting!!

RE: Excel 2007 Automatically changing formulas?!

I think this is dangerous as well.  I've seen this before and couldn't figure out what was happening.  I think the default ought to be unchecked, and that checking that box should produce a big red letter warning.  

The paragraph from the help does not explain how this feature behaves.

Last, I went one step further with my version of Excel 2003 SP3.  When I went and deleted the number in cell D1, the formula did not change back.

IRstuff, can you please inform Bill about this?


Regards,

chichuck
 

RE: Excel 2007 Automatically changing formulas?!

IFRS - In 2007 I get the behaviour you describe if the "extend formulas and formats" option is turned off, or if it is turned on but the formula only covers two rows.

If the option is turned on, and the formula covers three or more rows it automatically etends the formula without asking and without warning (unless you call briefly highlighting the cell with the formula a warning).

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Excel 2007 Automatically changing formulas?!

Funny...I tried it and the auto extend didn't activate, even when the box in the edit tab was still checked.  Could it be that Bill has been listening all this time? bigears

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close