Forum Discussion
How to stop Excel from rounding to nearest quarter?
As you can see in the attached, the share totals for this sample set are all incorrect. I do not know how to show significant digits, but there are 15 decimal places shown in the file. I did try using the Round function, but it did not change the result.
ld wrote: ``the share totals for this sample set are all incorrect``
What do you expect them to be?
In your original posting, you assert that 504030 (B6) times 19.48604% [sic] (E6) should be 98215.4874, whereas the Excel result is 98215.50000 [sic].
First, 504030 times 19.48604% is, in fact, 98215.487412. And the Excel result is 98215.5000001492.
Second, based on the Excel results, I suggested that perhaps E6 is actually 19.4860424974704%.
I suggested that you display more decimal places. And when you did that, we see that E6 is actually 19.4860424975% -- very close to my prediction.
The bottom line is: your manual calculations differ from the Excel calculations because you rounded (or truncated) the actual values when you entered them into the calculator.
-----
It is unclear how to advise you.
In your original posting, you say that ``These numbers NEED to be exact``.
And they are, based on the exact percentages that you enter into columns C and D.
If you would like column E to be rounded to 5 percentage decimal places, as you display in your original posting, the formula in column E should be of the form (E6 for example):
=ROUND(D6-C6, 7)
because 19.48604% is the decimal number 0.1948604.
But that begs the question: should the constants in column C (and D) also be rounded to 5 percentage decimal places?
And if you would like column F to be rounded to 4 decimal places, as you display 98215.487412 in your original posting, the formula in column F should be (F6 for example):
=ROUND(E6*B6, 4)
I cannot say with impunity that that gives you what you expect, because you did not show what you expect for all of the examples in your attached Excel file.
I suggest that you apply those rounding changes, then examine all of your data to be sure you are getting the result that you want.
Post any counter-examples (in an Excel file attachment) that do not match your expectations.
Alternatively, adjust your expectations based on truly exact calculations, which Excel gives you.
Bottom line, again: it is not Excel that arbitrarily rounds calculations to anything, much less multiples of 0.25.