Software

Office solution: A few reasons why Excel formulas might not calculate as expected

This week, we learn the answer to last week's Office challenge: Why aren't my formulas calculating correctly?

Last week, we shared a sheet that wasn't calculating correctly. Specifically, the first formula worked fine, but when we copied it, the formula returned the result of the first formula. Why might a sheet behave so unexpectedly?

In the example sheet, I entered a SUM() function to total each salesperson's commission (row 8) and bonus (row9). Nrobinson5 suggested that the formula uses absolute references: instead of =SUM(B8:B9), I entered formula =SUM($B$8:$B$9). If I had used absolute references, the formula would return the same total for each salesperson, but that's not what I did. If you check the Formula bar in the figure, you can see that I didn't use absolute references (I apologize if you can't make that out!)

I'm glad Nrobinson5 brought up this possibility though. It's a great place to start troubleshooting and could easily have been the problem.

Dogknees was the first to mention that the calculation method might be set to Manual - and that is the problem, but as bill.kuunders mentioned, it gets worse. This isn't a case of the calculation switch being flipped for a single workbook. Once you turn it on, it's on - even if you don't save the workbook! That means, the next workbook you open will also be set to manual, even if that's not what you meant.

When the calculation mode is set to manual, Excel displays Calculation in the status bar, but a lot of users won't even notice it, let alone recognize it. It's a troublesome feature that generates a lot of support calls. You must remember to switch the calculation mode back to Automatic.

In Excel 2010, this option is on the File menu. Choose Options under Help, and then choose Formulas in the left pane. Make sure Automatic is selected in the Calculation Options section, unless of course, you have a specific reason for using manual (which is possible, but unusual).

Defred601 mentioned an interesting paste behavior: if you use the wrong paste option, Excel will paste the evaluated result and not the formula. While this doesn't seem like a likely possibility to someone who's familiar with Excel, a rookie user might make this mistake.

Thanks everyone for another great challenge!

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.

Editor's Picks