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 formula

The solution is to use Excel’s ROUND, ROUNDUP or ROUNDDOWN formulas.

  • 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 formulas can be applied to a cell, combined with other formulas or even contain their own calculation.

Let’s take the ROUND formula 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 our ROUND formula 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.

Excel ROUND options

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 formula with another formula.

ROUND with another formula

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 formula.

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 formulas here too.

Excel ROUND to fraction

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 formulas 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 etc. using the shortcuts below.

Share This

Please share this or leave a comment and I'll make sure you get a personal reply.

Leave a Comment

Current day month ye@r *

{ 20 comments… read them below or add one }

Jolene November 10, 2010 at 12:16 pm

thanks, I appreciate the time you took to write this, its all very clear with the different colours and images :)

J

Reply

Rick March 31, 2011 at 12:12 am

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.

Reply

Mynda March 31, 2011 at 9:08 pm

Thanks Rick. Glad I could help.

Reply

sean June 18, 2011 at 9:56 am

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?

Reply

Mynda June 18, 2011 at 12:35 pm

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.

Reply

Scott October 26, 2011 at 11:01 pm

Thank you! This was a great help!

Reply

Gary November 3, 2011 at 1:02 pm

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.

Reply

Mynda Treacy November 3, 2011 at 9:10 pm

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.

Reply

lalit August 26, 2012 at 12:19 am

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…?

Reply

Mynda Treacy August 27, 2012 at 8:18 am

Hi Lalit,

=IF((RIGHT(A1,2)=”99″),ROUND(A1,0),ROUNDDOWN(A1,0))

Where A1 contains your value 50.99

Kind regards,

Mynda.

Reply

vicktor schausberger November 24, 2012 at 7:51 am

I would line to round average that IF 0.50 round down and IF 0.51 round up

Reply

Mynda Treacy November 29, 2012 at 7:48 pm

Hi Vicktor,

You can use this formula:

=IF(RIGHT(A1,2)< ="50",INT(A1),ROUND(A1,0))

Kind regards,

Mynda.

Reply

Abraham Kasika March 11, 2013 at 10:53 pm

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

Reply

Mynda Treacy March 12, 2013 at 1:39 pm

Thanks for your kind words, Abraham :)

Reply

Lala April 8, 2013 at 6:23 am

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!

Reply

Carlo Estopia April 8, 2013 at 3:48 pm

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

Reply

Hafizullah July 25, 2013 at 10:08 pm

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.

Reply

Mynda Treacy July 25, 2013 at 10:32 pm

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.

Reply

Ann Hilton August 16, 2013 at 8:25 pm

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!

Reply

Mynda Treacy August 16, 2013 at 8:27 pm

Thanks, Ann. Nice of you to take the time to leave a message. I’m glad I could help :)

Reply

Previous post:

Next post: