SOLVED

Stop Excel from Rounding to the Nearest Dollar Amount

Copper Contributor

39*$13.33 should = $519.87 NOT $520.00

 

I have searched relentlessly for help on this problem to no avail. My decimal points are set correctly, my column is wide enough, yet no matter what I do, the cell marked in red multiplies the answer to the nearest dollar instead of showing the cents correctly. I do know that it may be in part that the "Rate" (C21) is the result of another formula which is somehow affecting it. If I create the formula without the preceding formula, the cents show up not rounded to the dollar. I have spent hours and days trying to figure this out.

 

6 Replies
best response confirmed by tristatecom (Copper Contributor)
Solution

The issue lies in the rate in cell C21. Essentially you are taking $520 divided by 39 which actually gives an answer of 13.333... Then you multiply 13.333... again by 39 which gives you back your original $520.

 

You should use the following formula in cell C21 if you desire $519.87 as a result in cell D21:

=ROUND(E14/B21,2)

 

Hope this helps!
PReagan

You are a life saver!@PReagan 

 

Thank you so much. 

You are welcome :)

You are a life saver!@PReagan 

 

Thank you so much. If you have a minute, can you explain that formula briefly. I would never have quessed to pick ROUND and the "2" must be the places?

Precisely! The syntax for ROUND() is ROUND(number,num_digits). So simply ROUND() rounds a number to a total of num_digits.

@tristatecom 

Hi,

The "Rate" column is wrongly set to "Accounting" format

Set it to General format>> Then in cell C21 type =Round(E14/B21,2)

 

Mind the Cell locking of E14 if you are copying this formula

 

Good Luck

Hope that helps

Nabil Mourad

1 best response

Accepted Solutions
best response confirmed by tristatecom (Copper Contributor)
Solution

The issue lies in the rate in cell C21. Essentially you are taking $520 divided by 39 which actually gives an answer of 13.333... Then you multiply 13.333... again by 39 which gives you back your original $520.

 

You should use the following formula in cell C21 if you desire $519.87 as a result in cell D21:

=ROUND(E14/B21,2)

 

Hope this helps!
PReagan

View solution in original post