How to stop Excel from rounding to nearest quarter?

[ld]

I am attempting to use Excel to calculate percentages and the totals are all rounding to the nearest quarter. This isn't going to work, and the work arounds I've found don't seem to have worked either. This never used to be an issue.

We are multiplying $504,030 by 19.48604% and it is returning a value of $98,215.5000 when the real final value should be $98,215.4874... How do we stop this from happening?? These numbers NEED to be exact and nothing seems to be working. 

Total	Share %	Share Total (per excel)
$504,030	19.48604%	98215.50000

Total	Share %	Share Total (manually calculated)
$504,030	19.48604%	98215.4874

[#2]

@ld 

You may

=MROUND(A1*B1,0.25)

[#3]

@Sergei Baklan 

That returned the same number, 98215.50000

[#4]

@ld 

Excel does not normally round to the nearest 0.25.

So please provide an Excel file that demonstrates the problem.

Click "browse" at the bottom of the reply window.  Alternatively, upload the example Excel file to a file-sharing website, and post the download URL.

Refer to the attached image.

Format all cells to display at least 15 significant digits (not the same as 15 decimal places).

Row 2 shows your calculation, with the numbers as you posted them.

I wonder if they have more precision than you posted.

Row 3 shows what 19.48604% [sic] might really be, based on exactly $504,030.

Row 4 shows what $504,030 [sic] might really be, based on exactly 19.48604%.

A combination of numbers in between is also possible.

[#5]

@Joe User 

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. 

[#6]

@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.