SOLVED
Home

Stop Excel from Rounding to the Nearest Dollar Amount

%3CLINGO-SUB%20id%3D%22lingo-sub-773992%22%20slang%3D%22en-US%22%3EStop%20Excel%20from%20Rounding%20to%20the%20Nearest%20Dollar%20Amount%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-773992%22%20slang%3D%22en-US%22%3E%3CP%3E39*%2413.33%20should%20%3D%20%24519.87%20NOT%20%24520.00%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20searched%20relentlessly%20for%20help%20on%20this%20problem%20to%20no%20avail.%20My%20decimal%20points%20are%20set%20correctly%2C%20my%20column%20is%20wide%20enough%2C%20yet%20no%20matter%20what%20I%20do%2C%20the%20cell%20marked%20in%20red%20multiplies%20the%20answer%20to%20the%20nearest%20dollar%20instead%20of%20showing%20the%20cents%20correctly.%20I%20do%20know%20that%20it%20may%20be%20in%20part%20that%20the%20%22Rate%22%20(C21)%20is%20the%20result%20of%20another%20formula%20which%20is%20somehow%20affecting%20it.%20If%20I%20create%20the%20formula%20without%20the%20preceding%20formula%2C%20the%20cents%20show%20up%20not%20rounded%20to%20the%20dollar.%20I%20have%20spent%20hours%20and%20days%20trying%20to%20figure%20this%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-773992%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774034%22%20slang%3D%22en-US%22%3ERe%3A%20Stop%20Excel%20from%20Rounding%20to%20the%20Nearest%20Dollar%20Amount%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774034%22%20slang%3D%22en-US%22%3EThe%20issue%20lies%20in%20the%20rate%20in%20cell%20C21.%20You%20should%20use%20the%20following%20formula%20in%20cell%20C21%20if%20you%20desire%20%24519.87%20as%20a%20result%20in%20cell%20D21%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DROUND(E14%2FB21%2C2)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774075%22%20slang%3D%22en-US%22%3ERe%3A%20Stop%20Excel%20from%20Rounding%20to%20the%20Nearest%20Dollar%20Amount%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774075%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20are%20a%20life%20saver!%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774076%22%20slang%3D%22en-US%22%3ERe%3A%20Stop%20Excel%20from%20Rounding%20to%20the%20Nearest%20Dollar%20Amount%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774076%22%20slang%3D%22en-US%22%3EYou%20are%20welcome%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774078%22%20slang%3D%22en-US%22%3ERe%3A%20Stop%20Excel%20from%20Rounding%20to%20the%20Nearest%20Dollar%20Amount%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774078%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20are%20a%20life%20saver!%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much.%20If%20you%20have%20a%20minute%2C%20can%20you%20explain%20that%20formula%20briefly.%20I%20would%20never%20have%20quessed%20to%20pick%20ROUND%20and%20the%20%222%22%20must%20be%20the%20places%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774084%22%20slang%3D%22en-US%22%3ERe%3A%20Stop%20Excel%20from%20Rounding%20to%20the%20Nearest%20Dollar%20Amount%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774084%22%20slang%3D%22en-US%22%3EPrecisely!%20The%20syntax%20for%20ROUND()%20is%20ROUND(number%2Cnum_digits).%20So%20simply%20ROUND()%20rounds%20a%20number%20to%20a%20total%20of%20num_digits.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774086%22%20slang%3D%22en-US%22%3ERe%3A%20Stop%20Excel%20from%20Rounding%20to%20the%20Nearest%20Dollar%20Amount%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774086%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382267%22%20target%3D%22_blank%22%3E%40tristatecom%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EThe%20%22Rate%22%20column%20is%20wrongly%20set%20to%20%22Accounting%22%20format%3C%2FP%3E%3CP%3ESet%20it%20to%20General%20format%26gt%3B%26gt%3B%26nbsp%3BThen%20in%20cell%20C21%20type%20%3DRound(E14%2FB21%2C2)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMind%20the%20Cell%20locking%20of%20E14%20if%20you%20are%20copying%20this%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGood%20Luck%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E
tristatecom
New 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
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