Excel giving me wrong percentage answer

[Ktyn111]

I am trying to find % contribution. I know the answer to %18.29/$43.36 should equal 0.42181 which would be 42.18% but excel is giving me the answer 42.1877 rounding it to 42.19%. My answer needs to be 42.18%. How do I fix this?

 

[keelaboosa]

Make your formula =ROUND(B70,2)/ROUND(B61,2)

 

[Ktyn111]

How do i fix this? Sorry I am no expert in excel at all whatsoever.

 

[jtakw]

Make your formula =ROUND(B70,2)/ROUND(B61,2)

I don't know if that's a good idea in all circumstances, since it actually will produce the wrong answer; however, since in this case, we're dealing with Small amount of currency, that may be negligible.

 

[jtakw]

How do i fix this? Sorry I am no expert in excel at all whatsoever.

If you're not dealing with huge amounts of money, you can consider keelaboosa's suggestion, or you can do the ROUNDING at the source formula.

 

[joeu2004]

I am trying to divide $18.29/$43.36. The correct answer is 0.421817 which is 42.18%. But when I type the formula into excel (B70/B61) it is giving me 0.421877 which is making the percentage be 42.19%.[

Make your formula =ROUND(B70,2)/ROUND(B61,2)

That is indeed the right answer, and it is the correct arithmetic in all cases with all dollar amounts (that are $9,999,999,999,999.99 or less )

@Ktyn111.... You are confusing how the cell values appear (18.29 and 43.36) with what their actual values are.

If you temporarily format B70, B61 and the quotient to display 13 decimal places, you will see that one or all actual values are not 18.29, 43.36 and 42.1877%.

In particular, B70 is between 18.285 and 18.2949999999999, B61 is between 43.355 and 43.3649999999999, and the quotient is between 42.18765% and 42.1877499999999%.

If you want to divide the values as they appear, use the ROUND expressions that kellaboosa provided.

Alternatively, you might consider explicitly rounding the calculations in one or both cells. That is, change =expression to =ROUND(expression,2).

We choose 2 decimal places because that seems to be the accuracy that you expect, based on the cell format and the expectations that you document in this thread. In general, you should round to the degree of accuracy that you expect.

 

[keelaboosa]

I've run into this issue myself. Sometimes it's not clear when you should round answers - before or after a calculation. Maybe you need a percentage based on values that could be proven by timecard stubs - maybe it's more complicated than that. The short answer to when to round is: "It depends"

 

[joeu2004]

I've run into this issue myself. Sometimes it's not clear when you should round answers - before or after a calculation. Maybe you need a percentage based on values that could be proven by timecard stubs - maybe it's more complicated than that. The short answer to when to round is: "It depends"

Yes, it does. It is a judgment call.

What I was saying was: Ktyn clearly indicated a desire to calculate the percentage based on the rounded values that appear in B70 and B61. In that case, Ktyn should explicitly round those values.

(Of course, after this discussion, Ktyn might have second thoughts and realize that the he/she should calculate with the precision of the actual values.)

We have no information (from Ktyn) to tell us whether or not Ktyn should round the calculations in the cells themselves. That is why I said only that Ktyn should "consider" it.

In general, when we expect a calculation to be accurate to some number of decimal places, we should explicitly round the calculation to that number of decimal places.

Whether we round the result of the calculation in the cell or wherever it is referenced depends on our needs.