Tom’s Tutorials For Excel: Rounding Numbers By Fractions or Decimals
When rounding numbers to a particular decimal factor, you can express that rounding factor in your formula as either a fraction or as its decimal equivalent. In the pictures, the fraction one-eighth can be stated in a formula as 1/8 or by its decimal equivalent of .125.
Rounding up
Expressing a fraction, the formula in cell B4 copied to cell B8 is =CEILING(A4,1/8)
Expressing a decimal, the formula in cell D4 copied to cell D8 is =CEILING(A4,0.125)
Notice the results in range B4:B8 are the same as the results in range D4:D8.
Rounding down
Expressing a fraction, the formula in cell C4 copied to cell C8 is =FLOOR(A4,1/8)
Expressing a decimal, the formula in cell E4 copied to cell E8 is =FLOOR(A4,0.125)
Notice the results in range C4:C8 are the same as the results in range E4:E8.
We have three year wage scales in our contracts. To figure the scales, we put the current wage in excel and multiply (for example) by 2% every year of the scale and round the number to two decimals. However, some of the numbers end up being off by a penny in year two or three.
For example if you start with 25.37 and multiply it by 1.02 (25.88) and then that number by 1.02, you end up with 26.39. If you manually enter or start with 25.88 and multiply by 1.02 you get 26.40, which is the number it should be. Anyway to fix this issue?
Well Sue, you are asking a question that dogs computer users worldwide, when it comes to how digital math works with floating point rounding errors.
The short kinda-sorta answer to your question about any way to fix the issue you describe is, you can superficially “fix” it by setting your Excel’s calculation option to Precision as Displayed. This will, in your example, show a visual calculated result of 26.40 but really the actual number would be 26.394948 which was why you saw it rounded with 2 decimals to 26.39.
To set your workbooks to Precision as Displayed, click the File tab in the upper left corner of your workbook. Then click Options > Advanced > scroll down to the “When calculating this workbook” section, and select (put a checkmark in the box next to) “Set precision as displayed”. You will get an immediate message from Excel that data will permanently lose accuracy. You need to click the message box OK at that point no matter what. If you want to keep your Precision preference selected, click OK to confirm that option in the Excel Options dialog box. Keep in mind, this decision affects all workbooks, not just the one with your wage scales.
I’m biased but I think that my blog post explaining this very issue is an excellent resource, with examples to show how you are not alone with this kind of decimalized rounding. Check out my link here:
http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-why-are-my-numbers-adding-up-wrong/
Thank you, that is very informative. One question, if I change the setting to Set precision as displayed now, will it change all of my previously saved workbooks? Or can I change it and create a new workbook to see how it performs?
Thanks so much for your help!!
It will not change your previously saved workbooks as long as you keep them closed during the time you temporarily set precision as displayed and, which I strongly recommend, remembering to deselect that option before closing whatever workbook you wanted that feature to be effective for.
Before you do that, if I were you, I’d consider a different approach, **if** the issue is that the rounding needs to be done to the next highest hundredth decimal point. For example, suppose you keep Precision as displayed deselected (meaning, it’s the same as it always was before you posted your first question here), and further suppose you want this visual result:
# Col A Col B Col C
1 Start Add2% Add2%
2 25.37 25.88 26.40
That can be accomplished by inserting a CEILING function for rounding up the calculated decimals to the next highest hundredth decimal.
Using your original example, in cell A2 is 25.37.
In cell B2 is the formula =CEILING(A2*1.02,0.01)
In cell C2 is the formula =CEILING(B2*1.02,0.01)
Something to ponder without resorting to the precision as displayed option.
Unfortunately, not all numbers get rounded up; some need rounded down. I’m going to stick to re-entering the numbers after the first calculation or using the good old calculator! Thanks for your help!