Follow this blog:: RSS; Email Alert

Microsoft Office

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

September 14, 2011, 7:50 AM PDT

Takeaway: 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?

Get IT Tips, news, and reviews delivered directly to your inbox by subscribing to TechRepublic’s free newsletters.

About Susan Harkins

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.

Office solution: Why the reverse print option might not work

Use custom formats in an Excel chart's axis and data labels

Comments

Add Your Opinion

Join the conversation!

Follow via:: RSS; Email Alert

See All Comments

Staff Picks
Top Rated
Most Recent
My Contacts

No messages found

5 Votes

+ -

Is it because ...

nrobinson5@... 14th Sep 2011

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)

View in thread

5 Votes

+ -

Wrong paste used

defred601@... Updated - 15th Sep 2011

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.

View in thread

4 Votes

+ -

office challenge

bill.kuunders@... 15th Sep 2011

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.

View in thread

0 Votes

+ -

Updating the spreadsheet after a formula change

mrsmithatraytheon 28th Sep 2011

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

View in thread

0 Votes

+ -

copying

dhays Updated - 20th Sep 2011

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.

View in thread

0 Votes

+ -

This is a Major Excel Problem

rbowser@... Updated - 20th Sep 2011

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.

View in thread

Once logged in, adding contacts is simple. Just mouse over any member's photo or click any member's name then click the "Follow" button. You can easily manage your contacts within your account contacts page.

See all comments