Software

Office challenge: Why is SUM() returning different results for the same values?

Learn the answer to last week's Excel challenge and try your skills at another Excel challenge--the mysterious SUM() function evaluates the same values but returns different results!

Two SUM() functions evaluate similar values but return different results—how can this be? For instance, the SUM() functions in cells B4 and C4 both evaluate the values 1, 1, and 1. The function in cell B4 returns the correct result, 3, but the function in cell C4 returns 2. What's the problem (and there might be more than one cause)?

Last week we asked…

Why is Excel creating two decimal places for input values? BJWiltzius was the first to respond with the correct answer: Someone has enabled Excel's Fixed Decimal Places option. When this option enabled, Excel adds decimal places to every input value, regardless of your intentions. For example, if the option is set to 2, Excel changes an input value of 123 to 1.23. Enabling the feature is a good way to facilitate quick data entry when consistent decimal values are required. Most likely you'll enable it as needed and then turn it off when you don't. Forgetting to disable the feature often causes confusion the next time someone tries to enter values. Checking the cells' format is the key to quickly identifying the problem. Very few workbooks will be improved by enabling the feature permanently.

In Excel 2003, you'll find this option, Fixed Decimal Places, as follows:

  1. Choose Options from the Tools menu.
  2. Click the Edit tab.

In Excel 2007 and 2010, do the following:

  1. In Excel 2007, click the Office button and then click the Excel Options button. In Excel 2010, click the File tab and then click Options under Help.
  2. Select the Advanced option in the left pane.
  3. In the Editing section, check or uncheck the Automatically Insert A Decimal Point option.

This is an application option and works with all new workbooks once changed. Congratulations to BJWiltzius for the quick and correct response and thanks to everyone for playing along!

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