Hello and welcome to yet another #formulafriday blog post. I want to share today with you an Excel formula problem a colleague of mine asked me to look at a week or so ago. So, I thought it would be worth sharing here with you.
If you want to download the workbook for the #formulafriday 2019 Season then click on the link below.
[ddownload id=”14616″]
I was asked to look at worksheet which calculated the price of goods, a discounted price of goods and the price of goods converted from Sterling to Euros. On manually checking their results, (with a calculator), they saw a significant difference in their expected results – £384.48.
After assuming the difference was due to what they described as ’rounding’, they went ahead and changed the number of decimal places that Excel displayed by selecting to decrease the digits shown, but the results stayed the same.
So, Why Is This Happening?
This is happening due to the fact that Excel is simply is rounding the numbers for display purposes, but the formula is calculating the actual cell content, and then rounding the result for display only.
What Is The Solution?- use the ROUND function.
Yes, you can use the ROUND function in Excel to ensure your results are what you expected. It is really simple to use with the following syntax
=ROUND(the number you want to round, the number of decimal places to round your number to)
A Simple Example.
Cell A1 contains 1.254, cell A2 contains 1.253, if we sum those two numbers we see 2.507. If we try to display only two decimal places both cells display 1.25 and the total is 2.51, but we know that 1.25 plus 1.25 equals 2.50, not 2.51. So if we round the numbers at the source to 2 decimal places, get the expected result.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Formula Friday series Click Below
How To Excel At Excel – Formula Friday Blog Posts.
