The truth is that while Microsoft Excel has been around for many years, with many different versions, sometimes, there are bugs. Unfortunately, these bugs may mean that you are doing the wrong calculations that may affect you, your work, or even your overall life.
One of the main problems with solving these bugs is that in many cases, people just can’t replicate the bug. So, how can we solve something if we don’t even see it?
When you are using the Excel ROUND function, you may experience some of these so called bugs.
To show you how Excel formatting can change a displayed value but not the real value, you just need to follow the next steps:
1 – Open your Microsoft Excel. It doesn’t matter the version you have.
2 – In a new worksheet, pick a cell and write the number 1.987654.
3 – Now, just select this cell and click repeatedly on the Decrease Decimal toolbar/icon that you can find on the Home Tab/Number Group. Now, just watch the changes to the display until the value shows as 2.0.
4 – Look in the formula bar to verify the original value hasn’t changed; this value is what Excel uses in formulas. The displayed number of 2.0 is what your readers will likely use if they audit your work.
When you select a popular number formatting option in Excel like currency or comma, Excel will round the displayed results to the specified number of decimal places such as 2 or 0. The obstacle is that these formatting actions do not change or round the value behind the result to the same number of decimal places. Instead, Excel retains the full value as it is passed on to other formulas in the worksheet.
The Problem Is Worse For Some People
While most people only tend to use whole numbers, dollars, and cents, you won’t probably need to deal with this problem. However, some jobs depend on the accuracy of a software such as Microsoft Excel to perform their tasks. Just think of accounting and financial applications.
As you can easily understand, these use a wide range of formats such as fractional shares, percentages, only to name a few.
These calculation problems tend to be common, for instance, in oil and gas and other industries where volumes, lease ownerships, prices, and other factors will often be quantified out to 3, 6, 12, or more decimal places.
What’s The Answer?
Well, the answer to this problem is plain and simple: Excel ROUND function. To ensure that the displayed answers match the values behind the results, you just need to ensure that you add the ROUND function to all your formulas.
Simply put, when you do this, you can calculate your values using the same number of decimal places that you want to display in your worksheet.
In case you don’t know, the basic structure for the ROUND formula is:
=ROUND(formula, # of decimal places)
In case you’re familiar with the function, then you probably noticed that I changed it a bit. The truth is that the official formula according to Excel is: