It’s common practice in Excel to format decimal places to get the desired rounding of numbers. But the problem with this approach is it can display totals that don’t appear to add up correctly.
Take the screen shot below that shows 2+2+3=8. That’s because the underlying numbers are actually 2.4+2.4+3.2.
 |
Excel ROUND Formulas
The solution is to use Excel’s ROUND, ROUNDUP or ROUNDDOWN functions.
- ROUND rounds a number to a specified number of digits
- ROUNDUP rounds a number up, away from zero
- ROUNDDOWN rounds a number down, towards zero
Let’s take the number 2.4 and round it to no decimal places as an example.
Using ROUNDUP you’ll get 3.
Using ROUNDDOWN you’ll get 2.
Using ROUND you’ll also get 2. ROUND will round down anything under 5, and round up anything 5 and over.
How to enter a ROUND formula
The ROUND, ROUNDUP, ROUNDDOWN functions can be applied to a cell, combined with other functions or even contain their own calculation.
Let’s take ROUND on its own first.
The Excel sytax is
=ROUND(number,num_digits)
In English it means:
=ROUND(cell reference (e.g. C2) or calculation (e.g. 5.3+2), the number of decimal places you want)
In the image below we can see ROUND in the formula bar as
=ROUND(C2,0)
Note: You can see that even though I’ve told Excel to round my number to zero decimal places in cell G2 it’s still displaying the number as 2.0. This is simply because the cell formatting is to one decimal place.
 |
Let’s say I wanted to round the number 2.489 to 2 decimal places. My formulas would read:
=ROUND(2.489,2) would give you 2.49
=ROUNDUP(2.489,2) would give you 2.49
=ROUNDDOWN(2.489,2) would give you 2.48
Ok, so that’s pretty easy. Let’s look at how we’d use a ROUND function with another funciton.
ROUND with another function
ROUND a SUM to no decimal places =ROUND(SUM(C1:C10),0)
You can see the ROUND formula is wrapped around the SUM formula.
ROUNDUP with IF =ROUNDUP(IF(C10>=450,C10*9%,""),0)
ROUNDDOWN with AVERAGE =ROUNDDOWN(AVERAGE(C1:C10),0)
You’re not limited to these examples above, ROUND can be used with almost any function.
ROUND to the nearest 5 cents
What say you priced products in 5 cent increments, but you found that when you marked up the cost price you often ended up with an amount that didn’t end in a 5 or a whole number. Take the example below where the selling price calculates at $9.96, but you have to price it at $9.95 or $10.00.
You can use the ROUND functions here too.
 |
By dividing the calculation by 5 cents (0.05) and rounding it to 0 decimal places, you can then multiply it by 5 cents (0.05) to get the correct amount.
If you wanted to round to the nearest 50 cents you would just replace the two instances of 0.05 with 0.50.
Alternatively you could use the CEILING or FLOOR functions to do this, but that’s a lesson for another day.
Download the workbook and practice what you've learnt.
Did you find this useful, or did it open a can of worms? Let us know by leaving a comment.
Share the knowledge with your friends and colleagues on Twitter, Facebook, Google+, LinkedIn etc.
S. Behen says
I think you may be the one to help me figure out a rounding formula for my upcoming price increase!
I need to perform the following rounding schedule and can’t figure out the formula.
Thanks in advance for your help!!!
$0.01-$0.99=Actual
$1.00-$5.99=Round up to the nearest $0.05
$6.00-$9.99=Round up to the nearest $0.10
$10.00-$19.99=Round up to the nearest $0.25
$20.00-$49.99=Round up to the nearest $0.50
$50.00-up=Round up to the nearest $1.00
Catalin Bombea says
Hi,
Try this one (the price should be in A1):
=CEILING(A1,IF(A1<1,A1,INDEX({0.05,0.1,0.25,0.5,1},MATCH(A1,{1,6,10,20,50},1))))
Suhail Amin Bhat says
Sir,
suppose i have the values like 2.2 3.1.3.2, 4.2
the round function gives values 2, 3, 3 and 4 respectively but if we sumup these values the value shall again be 13 instead of 12, how i can get the value 12
Mynda Treacy says
Hi Suhail,
If you wrap your SUM in the INT function it will drop the decimal places off the result, is that what you want?
Mynda
Jeff says
Great help……to gain an understanding of formulas
Catalin Bombea says
Hi Jeff,
Glad to hear you like our tutorials 🙂
Cheers
Catalin
Jennifer Carpenter says
Help!! Need to resolve a rounding error. If cell T3 is rounded and O3 is not, but a whole number … How do I adjust the formula to account for a rounding issue? Basically if its less than a dollar variance the formula should not reflect the “check” error.
Formula I’m currently using is: =IF(T3>O3,”check”,”-“)
Mynda Treacy says
Hi Jennifer,
You can try this formula:
Mynda
Rohit Kumar says
It opened a can of worms coz when my problem was solved i jumped too hard and it opened a can of worms.
Tammy says
Rounding on a spreadsheet. New Sales taxes rule is now requiring my spreadsheet to round up or down. If an amount that ends .49 cents or lower, then round amount down to the previous dollar, for the amount is .50 cents or higher, then round amount up to the next dollar.
Please help me with the rounding formula.
Here are a couple example of some sums on my spreadsheet
=SUM(E9:E10)
=SUM(K9*0.03)
Thanks for the help.
Mynda Treacy says
Hi Tammy,
You can use teh CEILING and FLOOR functions for this:
https://www.myonlinetraininghub.com/excel-ceiling-and-floor-functions
Mynda
John says
Know one seems to understand. I want the answer to a formal to always Round up to a whole number.
Catalin Bombea says
Hi John,
Have you tried =ROUNDUP(A1,0) ?
Catalin
Edmund Weil says
Hey Mynda,
Bit of a strange question here, but if anyone an answer it you can!
I have designed a stock taking sheet for my bar that doubles up as an order sheet for our suppliers.
Obviously stocktaking requires accuracy to .1 of a bottle, whereas an order has to be rounded to the nearest whole bottle. So far, so easy right? SO the formula goes like this: =ROUND(C14-D14) where C14 is par stock level and D14 is current stock level.
However where it gets complicated is for tope shelf/high value bottles which are sold infrequently we don’t want too much of hanging around in stock. As present, as long as the in-stock value is 0.5 or lower, there will be a command to order another bottle (rounding up from 0.5). I wonder is there any way to make it so that the formula will round DOWN from a value as high as say 0.7 but then round UP fro values of 0.8 or higher?
Thanks in advance for putting your knowledge and abilities to this.
All best,
Edmund
Catalin Bombea says
Hi Edmund,
You can check the remainder with MOD function, then apply the appropriate roundup or rounddown:
=IF(MOD(A1,1)>0.7,ROUNDUP(A1,0),ROUNDDOWN(A1,0))
Catalin
Harry says
Plz tell me the formula when
E.g.. (E6+F6)×113% in round figure
Mynda Treacy says
Hi Harry,
I assume you want it rounded to no decimal places. If so:
=ROUND((E6+F6)*113%,0)
Replace ROUND with ROUNDUP or ROUNDDOWN if required.
Mynda
Mark Lewis says
If I only want to round a vlaue if it is above 50,000.00 so 28,560.00 would be 0.00 but 504,288.00 would be 504,200.00 what =round formula should I use?
Catalin Bombea says
Hi Mark,
You should try this:
=IF(A1<50000,0,ROUNDDOWN(A1,-2))
Catalin
Sean Doss says
Hi Mynda,
I’m trying to round numbers to the nearest dollar, but I need to truncate the results so that I’m left only with an integer value. So if I’m adding three rounded numbers together, I don’t want the decimal amounts included in the total, just the rounded integer value.
To better illustrate, take the example below:
Amount 1 – $1.45 – rounded down, it would be $1.00
Amount 2 – $1.60 – rounded up, it would be $2.00
Amount 3 – $1.48 – rounded down, it would be $1.00
The exact total is $4.53. If I add the rounded values, I’ll get $5.00. However, I want the decimals removed once they have been rounded. So the answer I’m looking for is $4, the sum of the integers only resulting from the rounding action. Can you please help?
Thanks,
Sean
Catalin Bombea says
Hi Sean,
If you use Round on the sum of those 3 amounts, you will get 5. Use =Round($1.45,0)+Round($1.60,0)+Round($1.48,0), the result will be 4.
Cheers,
Catalin
paul says
Thanks Mynda,
If I was to add the rounddown to nearest 5 function to this formula =D13-(D13*E10) it’d look like:
=rounddown((D13-(D13*E10)/0.05,0)*0.05 ?
best,Paul
Mynda Treacy says
No, like this:
Paul says
totally stuck on how to round the following formula down to nearest 5, any suggestions?
=SUM((D$2*C13)*52)/12
Every combination I do it says I’ve added too few arguments… sorry guys!
Mynda Treacy says
Hi Paul,
You can use this formula:
No need for SUM unless you want to SUM a range of cells.
Kind regards,
Mynda
Nelson Ndigi says
There is an easier method to ROUND to the nearest 5 cents =MROUND(12365.042, 0.05) you get 12365.05
Mynda Treacy says
Thanks, Nelson. Great tip.
They (MS) really should have named that ROUNDM so it doesn’t get overlooked!
Mynda
gary says
YES!!!!!! THANK YOU VERY MUCH MYNDA, I HAVE FOUND A SOLUTION THAT HAS BEEN UNKNOWN TO ME AND A LOT OF MY CO-TEACHERS HERE IN YOUR SITE . NOW WE CAN USE THE FORMULA TO OUR HEARTS CONTENT, ELIMINATING UNNECESSARY LONG CUT. KUDOS TO YOU….. GOD BLESS
Mynda Treacy says
You’re welcome, Gary 🙂
Jane says
Thank you for this tutorial – just what I needed 🙂 I’m sure i’ll be back for more help !
Jane
Mynda Treacy says
You’re welcome, Jane. Be great to see you back here again sometime.
Ellie Perry says
Help! I need the formula to multiply a cell by 60% then roundup to the nearest $.25. I’ve entered it several ways but it rounds down 5.30 to 5.25.
Catalin Bombea says
Hi Ellie,
Use this
Catalin
Ann Hilton says
Very helpful, thank you. Having spent a good couple of hours trying to complete one function, your site helped me do it in a matter of minutes!
Mynda Treacy says
Thanks, Ann. Nice of you to take the time to leave a message. I’m glad I could help 🙂
Hafizullah says
Hi
My Dear honest teacher Mynda I hope you will doing well with your noble family.
Thanks for your nice Information you share with all people i appreciate you.
you know i am trainer of office program bot i am not professional trainer bot i hope to be a good teacher in the future if you help me.
please i hope you will accept my request i will waiting for your good news.
Thanks for again have a good time with your noble family.
Mynda Treacy says
Hi Hafizullah,
Thanks for your kind words. You can find a list of free tutorials on Excel Formulas here, plus have a read through our blog for other tutorials.
All the best.
Mynda.
Lala says
Hi!
I have the following situation:
I have a column with cells like this one
=5.83/11
= 7.88/11 and so on
I would like to add Round formula with copy-paste but keeping the original numbers, without doing it manually for each cell. Is it possible?
I’ve made one modification manually but when I use Paste Special – Formula it changes also the numbers (if I copy from the cell with 5.83 in the cell with 7.88, 7.88 changes in 5.83.
Thank you very much!
Carlo Estopia says
Hi Lala,
Honestly, I don’t quite get what you mean here.
Why don’t you send your file and label the things that happened.
Send it here: HELP DESK
Cheers,
CarloE
Abraham Kasika says
Mynda
Thank you for making this understandable and easy to follow. I am writing you this down in the jungle of DR Congo and there is no Excel help within a distance of at least 800kms, and that will not be in English! I will come back to you for more Excel related questions – if you do not mind.
Kind regards
Abraham
Mynda Treacy says
Thanks for your kind words, Abraham 🙂
vicktor schausberger says
I would line to round average that IF 0.50 round down and IF 0.51 round up
Mynda Treacy says
Hi Vicktor,
You can use this formula:
Kind regards,
Mynda.
lalit says
i want to set round function at .99 ..like if i have no.>=50.99 then it will return 51 else it will return 50. plz tell me how it is possible…?
Mynda Treacy says
Hi Lalit,
=IF((RIGHT(A1,2)=”99″),ROUND(A1,0),ROUNDDOWN(A1,0))
Where A1 contains your value 50.99
Kind regards,
Mynda.
Gary says
I entered a bunch of UPC numbers into an excel spread sheet for hundreds of my products. I saved it and closed it. When I opened it back up all the numbers with more than 11 digits changed to only keeping the first three numbers and then rounding down to all zeros for the rest of the numbers! This was hours of work. Can I get my original numbers back or do I have to enter them all again!? Help! 🙂
Thanks.
Mynda Treacy says
Hi Gary,
Have you tried to change the formatting of the cell to ‘Number’?
To do this press CTRL+1 > on the ‘Number’ tab select ‘Number’ from the category list.
Does this fix the problem?
If not send me your workbook by logging a ticket on the help desk and I’ll take a look.
Kind regards,
Mynda.
Scott says
Thank you! This was a great help!
sean says
does anyone know if you can apply the round function to a column of numbers such that you would not need to enter the formula in another column?
Mynda says
Hi Sean,
The only way you can apply any sort of rounding to an existing column of numbers (that are not formulas) is to use formatting, but as I mentioned above, this isn’t true rounding.
The other option you have is to insert the ROUND formula in another column, then Copy and Paste Special > Values back to your original column, then you can delete the column with the ROUND formula as it’s no longer required.
Alternatively, if the column that you want to round contains a formula you can wrap the ROUND formula around your existing formula.
For example;
=ROUND(A1/B1,2)
=ROUND(SUM(G11:G14),2)
There are more examples in the tutorial above.
I hope one of those solutions is suitable.
Regards,
Mynda.
Rick says
Had found a few examples on the internet – but this is the only one that really made it clear enough to add it to my word doc. Very easy to use – really fantastic.
Mynda says
Thanks Rick. Glad I could help.
Jolene says
thanks, I appreciate the time you took to write this, its all very clear with the different colours and images 🙂
J