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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies