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?
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?!
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?!
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Excel 2007 Automatically changing formulas?!
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?!
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?!
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?!
Tools | Options | Edit
in 2003.
RE: Excel 2007 Automatically changing formulas?!
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?!
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?!
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.
Read the Eng-Tips Site Policies at FAQ731-376: Eng-Tips.com Forum Policies
RE: Excel 2007 Automatically changing formulas?!
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?!
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?!
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?!
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?!
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Excel 2007 Automatically changing formulas?!
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?!
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?!
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?!
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!
RE: Excel 2007 Automatically changing formulas?!
RE: Excel 2007 Automatically changing formulas?!
<a href="http://www.authorizedinspector.com"><img src="ht tp://www.a uthorizedi nspector.c om/Images/ AILogo.gif" </a>
RE: Excel 2007 Automatically changing formulas?!
RE: Excel 2007 Automatically changing formulas?!
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?!
This is all academic for me because I use OpenOffice 99% of the time anyway. But, interesting!!
RE: Excel 2007 Automatically changing formulas?!
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?!
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Excel 2007 Automatically changing formulas?!
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?!