Microsoft

Office challenge: Why aren't my formulas calculating correctly?

This week, test your Excel skills against a workbook that suddenly stops calculating correctly.

You open a workbook to make a few enhancements. You enter a new formula and then copy it, but instead of displaying the expected results, the copied formulas display the result of the first formula.

Each formula in row 10 displays $7,806! What's going on?

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

16 comments
mrsmithatraytheon
mrsmithatraytheon

Hit the F9 key to update the fields with the correct "new" calculations.

dhays
dhays

I have found that in Excel 2010, when a cell or set of cells is copied, the copy is not always an exact copy of the original, expecially for dates expressed as text ('9/10/11) most of the time it is copied as a date (9/10/11) and therefore my countif function does not count the text so formatted. It is the same with plain text, sometimes it needs an appostrophe and sometimes not, to be counted with the countif function. It is kind of distressing and can cause miscalculations that can be overlooked if not watched for and corrected. The spreadsheet is not strategic as it is for information only, however I do want the information to be accurate. The original sheet was setup in E2003, I don't really want to have to start all over by putting the data into a new sheet created in E2010 for the first time, with new formulas, etc. Even for my FY 2012 spreadsheet, I just copied an old one and deleted the data. With calculations and references and graphs it would have taken quite a bit of time to recreate the setup. It could be something as simple as Excel not copying the data/formula as we needed or expected. I virtually always copy with ctl-c and paste with ctl-v, I don't use the F-Keys, I don't even know those shortcuts. I do drag and drop copying data and formulas and it does work most of the time. O2010 has been a challenge to work with, I wasn't totally unaware of the ribbon menus after using them at home in O2007. Some of the commands have been moved from where they were in O2003 and it takes a while to find what you need. And sometimes it seems you can't do what you need anymore either. I do like the table functions in Outlook, as I hadn't seen that before, I guess it just did the table to text feature without a command before.

rbowser
rbowser

Based on the hint from Office Challenge that you had opened the file after a file with manual recalculation set, I did some testing with one of my spreadsheets. After setting up a spreadsheet with manual recalculation and saving it, I opened one of my automatic recalculation sheets. Suddenly the old spreadsheet became a manual recalculation spreadsheet AND after closing Excel without saving either spreadsheet again, the old spreadsheet was now a manual recalculate when I reopened Excel and the old spreadsheet. I changed it back to automatic and closed Excel. Excel properly asked me if I wanted to save changes (note - it did NOT ask when I had closed after it changed my old spreadsheet to manual recalculation). Soooo, Excel will change spreadsheets to manual when a manual is open, and not ask if that is what you want for your old spreadsheet. YUK! Note: I'm still using Excel 2007.

RJevon
RJevon

I would disagree with defred601. The problem states specifically "...the copied formulas ...". Also the original formula in the screenshot is relative. I would say that workbook calculations have been set to Manual. This is a very handy mode when you are working with workbooks with many calculations that take several seconds to calculate. This is manipulated in Excel 2010 under File > Options > Formulas Tab > Calculation options section.

ldajnowski
ldajnowski

I checked formula options in Excel 2010 and auto calculation is turned on by default (The check box for auto calculation is even greyed out). So what is the actual resolution to this problem? Will the actual fix be posted? Thank you.

cecilled
cecilled

Agree with defred601 that the copy and paste operation probably pasted a value as opposed to pasting a formula. most often when coping formulas I will simply grab the fill bar in the lower right hand corner of the cell and just drag that formula to all the adjacent cells that need the formula repeated. This prevents this type of error from occuring

adundon
adundon

dogknees and BillKuunder are correct. Formulas are not set to recalculate. This happened to me once, and It look me half a day to figure it out. Click File/Office Button and Excel options. This should be the first option under "formulas" THanks to Zimmerwoman for the tip that F9 will recalculate manually. I did not know that.

zimmerwoman
zimmerwoman

I have what I call the evil Microsoft Mojo. Odd and intriguing anomalies happen to me frequently in Microsoft Office. Frequently in Excel, if I select the cell, press F2 to enter edit mode, and then press enter, the calculation will update. This is after I have requested and failed to receive a manual recalc with the F9 key, so it's not that. And we can see in the example above that the formula is relative and not absolute, so that's not it. It has something to do with the computer's critical situation detector because it happens with greater frequently when I am working on a tight deadline than when I am not. Sometimes my spreadsheets get too big for the machine I am working on, but that is not always the case when these things happen. I can't explain it as it is one of the mysteries of the universe. It's just a gift or a burden, as you choose.

defred601
defred601

The formula was set-up in cell B10. When copied to C10, D10 and E10, the Paste - Values was used instead of the normal paste. This would be an easy mistake to make, especially with the new paste format. The paste - values is right beside the paste command. The description said this happened right after a new formula was entered and copied, not that it happens all the time.

jamescox
jamescox

Taking a slightly bigger view of what the segment of the spreadsheet appears to be doing, the sum of rows 8 and 9 (Commission and Bonus) is what Smith, Jones and others are going to get paid. While I'm sure they would LOVE to get paid the sum of rows 2 through 9, the accountant who set up the workbook would surely get fired if he modified the row 10 formula and they were paid that much... Looking closer is always a good idea... even after something has been looked at closely once.

gurughantal
gurughantal

change the formula in B10 to =SUM(B2.B9). Then copy this formula to C10, d10, e10 ...

bill.kuunders
bill.kuunders

this books' calculation was not on manual but, you have opened this workbook after one that has the calculation set to manual and was not closed.

nrobinson5
nrobinson5

the formula has used absolute cell references e.g. b10 > =SUM($b8+$b$9) copy/paste or fill/right will always paste =SUM($b8+$b$9)

mike.walsh
mike.walsh

You might be on the right track from a conceptual point of view, but look at the numbers. The sum in row 10 is NOT the sum of cells from row 2 to 9, but only the sum of rows 8 and 9. You need to look a little closer before answering.

jamescox
jamescox

Having been burned a time or two with the Excel behavior you mention led me to using some of my valuable QAT space by adding the Automatic and Manual commands to it. This gives a quick visual indication of the calculation mode (the appropriate commands checkbox is checked) and a easy way to change the mode.