Rounded figures - calculations
We have calculations using figures that show up multiple times at varying scales, so they're entered in the spreadsheet in whole dollars and historically the difference calculations are based on that information, then linked and formatted in documents/presentations. In one spot, we quote them in millions, to one decimal. There was a 0.1 "error" visually, because the math of "rounded number 1 less rounded number 2" as shown in the document was not the same as "whole number 1 less whole number 2, then round resulting difference in document" happening in practice. Is there an efficient way to avoid this happening, without having to duplicate all whole numbers into rounded before doing calculations?
-
One formula I've used in the past is ROUND, as well as ROUNDUP and ROUNDDOWN. You simply add it to your formula and tell Excel/Wdesk how many decimal points to include, if any. Typically, you would use a 0, 1 or 2 in the function (e.g., =ROUND(A1,2)), but you can also use negative numbers to display values to the thousands, millions, etc. (e.g., =ROUND(A1,-3)) Note that A1 is simply a placeholder - the formula can be quite complex if you want it to be. So while you will have to go in and manipulate your formulas initially to add ROUND, it will be there going forward so any updates will be calculated correctly.
0
I think this is a pretty common issue in reporting. The way that I avoid this issue, is to have all figures rounded to thousands in my workbooks, which is the lowest level in which we report numbers in our external financials and is the scale we use for our face statements. The difference calculations (dollars and % change) are always based on those numbers in thousands. When we write our MD&A explanations, they are usually quoted in millions or billions (using the scaling options in Wdesk), so because of rounding, it sometimes doesn't look right in the sentence, but the reader can always go back to our face statements to get the numbers in thousands and then the increase/decrease is therefore mathematically correct. Sometimes we need to explain it to auditors and reviewers, but this was the easiest way to manage those pesky rounding issues. Otherwise, I don't think you could ever have a fully automated way to make sure the rounding was correct every time. Also, if you change difference calculation to force the rounding, the number is actually not correct. This is much more apparent when quoting % changes.
0サインインしてコメントを残してください。
コメント
2件のコメント