Forum Discussion
DECIMALS IN CALCULATIONS CAUSE TOTALS TO NOT MATCH
In each section of the calculations worksheet, Expense * % is the formula, formatted to two decimal places. I then must manually key this result into another Excel worksheet (no way around the manual keying). Each section is totaled, then the sections are grand totaled. Typically, my calculations worksheet and the grantor's worksheet totals will not match by +/- a few cents. Is there a way -- the simpler the better -- to force the results in my calculations workbook to read as only the two decimal places that show when formatted as a number? I don't want to lose the mathematical rounding up/down occurring, I just want the total to be based on that two decimal place result, not the actual formulaic result? Thank you in advance for any assistance you can provide. ~Rhonda
2 Replies
If you want your calculation to match the manually keyed values then you would need to introduce small errors to the calculation to round the results
= SUM( ROUND(expense, 2) )
[You might need ROUNDDOWN if your manually keyed values are simply truncated rather than rounding some up]
Rhonda_Reid wrote: ``I don't want to lose the mathematical rounding up/down occurring``
But it sounds like that is, in fact, what is happening.
In the calculation worksheet, Excel displays rounded results. But the actual cell values are unrounded.
The dependent formulas use the unrounded cell values.
-----
For better answers, provide concrete examples of data and formulas. Ideally, attach an example Excel file, or post a link to an Excel file that we can download. Of course, the Excel file should be redacted of any private information (names, tax IDs, etc).
In order to achieve what-you-see-is-what-you-get results, you need to explicitly round calculations in each cell -- at least, each cell whose displayed value is copied to grantor's worksheet.
For example, instead of =A1*B1 + C1, write =ROUND(A1*B1 + C1, 2).
And that assumes that each of A1, B1 and C1 either contain constants (123.45) or formulas that are explicitly rounded.
