Excel Addition/Formula Help

[Lady Moreta]

Hello smart masses

I have an Excel question I'm hoping someone can help me with. I have not as yet googled it, because I haven't the faintest idea how to phrase it so that Google understands what I'm talking about.

Here's my problem.

There is a spreadsheet in Excel (Office 2010 I believe, Mac version) being used to work out salaries for a place I work at. The problem we're finding is that the spreadsheet is giving a total of $xxx.74, but when we add the numbers by hand (well, using a calculator), it comes out as $xxx.75. I know it's just one cent and that may not seem like an issue, but it's actually driving us crazy and is causing problems down the road when it comes to actually paying said salaries through the bank.

The spreadsheet is set up with the hours worked x pay rate, for each employee, then those are totalled into one grand total. All the cells are set to round to 2 decimal places, but in doing some number crunching, we've found that some totals are actually coming to 3dp. By which I mean, that when we've done the hours worked x pay rate on a calculator, some of the totals are coming to 3dp instead of the 2dp that Excel is showing.

My theory is that while Excel is showing only 2dp, when it's adding up the grand total it's adding all 3 decimal places. Does anyone know how to tell Excel to show 2dp and only add those 2dp as well (with appropriate rounding active of course)?

Is this making sense? Do you need more detail? And if all else fails, does anyone have any idea how I could phrase this as a google search to try and find some answers? If nothing else, can anyone tell me what's actually happening here? If I know what's going on, I can probably figure out a way to make it work the way I want it to.

Thanking you in advance :smallsmile:

[Malimar]

Yeah, setting it to display to X decimal points (under, for example, Format Cells > Number) doesn't actually round the number. It still remembers the entire number for the purpose of calculations.

The main way I know of to do what you want involves the ROUND() function (http://spreadsheets.about.com/od/excelfunctions/qt/070809_round.htm).

[Lady Moreta]

Yeah, setting it to display to X decimal points (under, for example, Format Cells > Number) doesn't actually round the number. It still remembers the entire number for the purpose of calculations.

Yeah, that's what I figured was causing the problem.

I did find this offered as a solution (http://office.microsoft.com/en-us/excel-help/set-rounding-precision-HA010218870.aspx) and I have a data set from the original spreadsheet to experiment on. I'm just loathe to specify anything as an accurate, this-will-always-work solution, when I'm not 100% certain what the problem is in the first place.

There are problems to being considered the go-to person for Office programs... :smallamused:

[Brother Oni]

There's two possibilities:


Your formulae are calculating to a greater than required number of decimal places which is resulting in a rounding difference. This is because of your multiplication step for 'hours worked x hourly rate'.

There's typos in one of your data entry cells that isn't being picked since everything is displayed rounded to 2 dp.


The 'Set Precision As Displayed' should fix the first issue, as would using the ROUND function appropriately.

For the second, I suggest setting the data entry cells to General formatting, so you can see exactly what's being entered. Speaking as someone who works in an industry where we have documentation exclusively dedicated to the rounding and reporting of data, I heartily recommend the practice.

The side effect is that your data entry won't look as pretty since the last '0' will be truncated, but I prefer that to hunting down which cell in thousands has a typo.

[Lady Moreta]

Thanks guys :smallsmile:

I'll try setting the cells to general formatting so I can check for typos, but I'm pretty sure that's not it. We've been over the darn blasted thing with a fine-toothed comb by this point. Then I'll give the other options a go... the real test will be next time wages have to be paid and we see if the error crops up again.

[Brother Oni]

I'll try setting the cells to general formatting so I can check for typos, but I'm pretty sure that's not it.


It's probably not the cause, but I still find setting the data entry cells to general to be useful for double checking values.



We've been over the darn blasted thing with a fine-toothed comb by this point. Then I'll give the other options a go... the real test will be next time wages have to be paid and we see if the error crops up again.

As others have mentioned, you'll still need a ROUND in the multiplication formulae or will need the 'Set Precision as displayed' option turned on in the worksheet, or you will get the same problem.

For this type of work (wages spreadsheet), the latter option is a good catch all fix. If you were dealing with things like compound interest or things where fractions of a pence mattered, then it would get more complicated.

[Jay R]

When using Excel, it is crucial to remember three things:

1. Computers are inherently stupid. All of your math is now being done by somebody who can only count to one.
2. What it shows you is not what it stores.
3. Any judgment call about how a situation should be handled was made years ago, at MicroSoft, by somebody who didn't know your situation.

You can choose to make the judgment calls, but you must learn how, and then explicitly make them yourself.

In this case, it isn't showing you the number it calculated; it's showing you a 2-digit approximation. But it saved the real number and will calculate it with that.

ROUND() may be the correct tool, but it might not. The company will decide how rounding is done. You may need FLOOR() or CEILING() instead

If you aren't careful, Excel merely allows you to make dumb mistakes much faster and more efficiently.

Excel calculations are no substitute for thought.

[Lady Moreta]

ROUND() may be the correct tool, but it might not. The company will decide how rounding is done. You may need FLOOR() or CEILING() instead

Don't suggest other functions when I don't even understand the ROUND one :smalltongue:

Fortunately, I am married to a maths teacher, and as he was looking over my shoulder as I was typing this, he promptly asked why I hadn't just asked him in the first place (answer: he wasn't around when the question was first raised) and he just now took me through the ROUND function. I will go back to my friend with a solution that I think should work.

Thanks for everyone's help :smallbiggrin:

[Asta Kask]

ROUND() may be the correct tool, but it might not. The company will decide how rounding is done. You may need FLOOR() or CEILING() instead

I hear that BEER () is a very useful tool when the computer screws you over.