Software optimize

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.

1 comments
BlueCollarCritic
BlueCollarCritic

Manual/Automatic/WTH: The Calculation setting is an Application level setting. That means that when its set to Automatic or Manual it is set within Excel and not an Excel workbook or worksheet. This means that if you do not have Excel open and you launch Excel by double clicking on an existing Workbook then when Excel has fully loaded and the workbook is open, the calculation setting is set to whatever that workbook was set to use. If you then open another Excel workbook without closing the existing one then the calculation mode for the second workbook will be set to the same as the first even if the 2 workbooks were saved with different calculation settings. References To An Absolute Mess: As a former instructor for a financial reporting tool that used Excel I am very familiar with the quirks of the Absolute and Relative cell reference and I dedicated no less than 1 hour to discussing them in the class I taught. The instruction does not end with clarifying what a Relative Cell reference is and what an Absolute cell reference is. Since the difference between the 2 has already been addressed let me just add to that the bits to keep in mind when using copy & paste in Excel for copy & paste. When you are copying and pasting a cell with a formula that has Relative cell references then those references are updated as the formula is pasted up/down and or left/right on the worksheet. If the formula has an Absolute reference then the reference remains no matter where you paste the formula. But did you know that you can have an Absolute reference that changes when you paste? If Cell A1 has the value 10 and cell B1 has the value 100 and cell A2 has the formula =$A$1 and you copy the contents of A2 and Paste into B2 the contents of B2 is =$A$1. If you copy and paste the value of cell A1 into B1 the contents of cell A2 remains the same. But if you were to CUT and paste the contents of A1 into B1 then the value in A2 would change from =$A$1 to =$A$2 . I call this the anchor effect. AN absolute reference is an absolute reference not to a cell but to the contents of a cell. This is why all Absolute references to a cell will change if you use CUT and Paste to relocate the contents of the cell being referenced. This may not seem like a big deal but in my class it made a big difference in how effectively the customers were able to use the product after they left the class. Customers were able to create new reports in minutes instead of hours by simply understanding Absolute & Relative cell references along with the Anchor effect in an Absolute reference.