Forum Discussion
Stop Excel from Rounding to the Nearest Dollar Amount
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.
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
6 Replies
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
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!
PReaganYou 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.
- You are welcome :)
