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.

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.

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.

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  
 

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/
 

It's on

Tools | Options | Edit

in 2003.

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!

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

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.

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

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

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

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

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

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

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

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

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  
 

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

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

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.

A colorful star for ColourfulFigsnDiags!

<a href="http://www.authorizedinspector.com"><img src="http://www.authorizedinspector.com/Images/AILogo.gif" </a>

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

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

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

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
 

I'll get right on that...

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

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/
 

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?