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.

Enter your email address below to download the sample workbook.

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

Andy

Hi,

Can you help me with a formula? 🙂

For exemple:

I have the flowing prices to round them 48.99, 57.99, 66.99 —>49.99, 59.99, 69.99

And for 43.99, 52.99, 11.99 —-> 44.99, 54.99, 14.99

And for 40.99, 80.99, 60.99 —-> 39.99, 79.99, 59.99

Thank you!

Andy

Mynda Treacy

Hi Andy,

You need to use the CEILING or FLOOR functions for this. If you’re still stuck, please post your question on our Excel forum where you can also upload a sample file and we can help you further.

Sue Hill

This opened up a can with more worms…trying to round in the following formula, x.xx5 numbers are rounded down…=ROUND(PRODUCT(H30,12.5%),2)

Mynda Treacy

Hi Sue,

Did you try =ROUND(PRODUCT(H30,12.5%),4)

Mynda

Craig

Hello,

How would I get excel to continue to round up the highest fractions in a column until it hits a specific number then round down the remain.

Example:

C1: 9.43, C2: 9.78, C3: 8.6

I want excel to round the highest fractions first until it equals 28. Then round the remain down.

So since C3 is the highest it rounds to 9, c2 is the second it rounds to 10, c1 is not needed to round up as we have hit our total so it rounds down.

Then I want to capture the fractions that it rounded up or down so they offset next month.

Example carryover:

C1= .43, c2=-.22, c3=-.4

Thank you,

Craig

Catalin Bombea

Hi Craig,

Hard to follow your logic, something is missing. Can you provide an example file with a manual result filled in? Use our forum (create anew topic after sign in)

Manfred Granig

I’m trying to create a rounding formula that from $0 – $3 rounds to the nearest 5c increment, from $3.01 – $5 to the nearest 10c increment, from $5.01 – $10 the nearest 50c increment. from $10.01 – $20 the nearest $, from $20.01 – $50 the nearest $5 increment and above $50 the nearest $10 increment.

Is this possible?

Catalin Bombea

Hi Manfred,

Try:

=CEILING(A1,INDEX({0.05,0.1,0.5,1,5,10},MATCH(A1,{0,3,5,10,20,50},1)))

Replace CEILING with FLOOR to see the results. CEILING will always roundup to the NEXT increment, FLOOR will round down to the next increment.

For example, if the value is 3.67, and the increment for this is 0.1, CEILING will return 3.7, FLOOR will return 3.6. The same results will be returned if the value is 3.62.

Manfred Granig

Thanks that’s awesome – works great!!!

simon

you just saved hours of messing around … thanks for the post

Catalin Bombea

You’re welcome, glad to help.

deepak

I search a formula to roundup & rounddown using if condition,

(if a1=>.50 then roundup to Rs. 1 else rounddown to Rs. 1)

Kindly help

Thanks

Catalin Bombea

Try this one:

=IF(A1>=0.5,ROUNDUP(…),ROUNDDOWN(…))

I guess that you do not mean A1>=0.50, do you mean just the decimal part?

If so, simply use ROUND(A1,0). By default, if the decimal part is above or equal to 0.5, a roundup will be applied, otherwise a rounddown is applied.

P Ross

$5,151.66

$24,490.51

$106.35

$4,931.73

$3,202.29

$3,501.54

$2,919.04

$545.37

$151.51

The above numbers have to total 45K, when rounded to a whole number they come to a value of 45,001. The general formula for rounding works except on the 24,490.51 this gets rounded to 24,491 which makes all the numbers added up to 45,001. What I need to to be able to round the above numbers to a whole number that will return 45K even. I have been trying to work on a roundup/round down if statement to try and make this work. Any help which be much appreciated

Philip Treacy

Hi Paulette,

Rounding 24,490.51 to 24,491 is correct.

Rather than use the ROUND function, just decrease the displayed number of decimals for each of your numbers. This will display them as rounded, but will add up to 45,000.

Regards

Phil

Future's Genius

Hi Every one

I’d like to thank every one for sharing the knowledge

** My Question is :

I Want a functions that round numbers to nearest 45 or 95 , For Example :

A (Original) B (Manually Entered)

1- 370 395

2- 3043 3045

3- 99 145

Best Regards,

Mynda Treacy

This post explains how to round to the nearest 45 or 95.

ASHOK KOUSHAL

10000 15000 20000

i want round off the three value in excel in what will the single value for these three value

Philip Treacy

Hi Ashok,

Not sure what you mean. Sounds like you want to get the average of these three numbers? Which would be (10000 + 15000 + 20000) / 3 = 15000

Regards

Phil

Matt Frandock

How would I make this formula roundup

=(H51/(1-K45))+D50+(C49*(H51/(1-K45)))

Mynda Treacy

Hi Matt,

You haven’t said how many decimal places you want to round up to, but assuming zero it would be:

Mynda

Rachel

Hi there,

I am trying to project the number of monthly sales per phase of product released. We release product for sale in phases, and I’m assuming a total of 4 sales a month among all phase releases. We currently have 8 phases available for sale. I created a formula to count the number of phases that are available for sale, and divide that number into the number of projected sales per month. As you can imagine, 4/8 equals .5 sales per phase per month, however showing half a sale isn’t realistic since sales need to be in whole numbers – so to address this I used the round function but this is now bringing me to 1 sale per phase per month for a total of 8 sales – double the realistic monthly projection. How can I get 4 out of the 8 phases to show 1 sale and the rest to show 0 sales, for a total of 4 sales per month? Is there a way to work from top to bottom – populating the earlier phase releases with the sales first until they run out of product and then working it’s way down to later phase releases? This is my current formula. Help much appreciated, thank you.

(ROUND((INDEX(Seasonality!$I$6:$T$42, MATCH(Template!$C$2, Seasonality!$H$6:$H$42, 1), MATCH(MONTH(Template!I$5),Seasonality!$I$3:$T$3, 0)))/COUNTIFS($F$6:$F$363, “=Remaining to Sell”, H6:H363, “>0”),0)

The first part of the formula is plugging the avg sales rate into my seasonality table which takes into account busy/slow seasons. For purposes here, the month in question has a projected sales rate of 4 as mentioned above. The second part is counting phases with product remaining to sell.

Catalin Bombea

Hi Rachel,

Can you please upload a sample file on our forum with a manual set of expected results? It will be easier to work on a file to provide a customized solution, rather than a generic answer.

You can create a new topic after sign-in, to upload the file and details.

Thanks

Lloyd

Great Explanation. I just found your website and I think it is great. I am trying to learn VBA but some of the things I want to do can be done without VBA coding. I found some of the answers I needed in your excellent website. Thanks

Lloyd

Mynda Treacy

Thanks, Lloyd! Glad we could help 🙂

Brian Jennings

im am rounding up at the moment but would like to round up and down in same cell if the sum is higher or lower than 1000.

this is the formula i am using currently

=ROUNDUP(SUM(E17-D17)/480,0)

Philip Treacy

Hi Brian,

You need to use an IF function too

This assumes:

– The number being rounded is in A1. Replace A1 with your SUM() or other calculation

– You are rounding to 1 decimal place

– Numbers greater than or equal to 1000 are rounded up. Your original qs did not specify what to do when the number being rounded was equal to 1000

Cheers

Phil

Muhammad Zohair

10>round up to the nearst whole number

100>roundup to the nearst multiple of 5

1000> roundup to the nearst multiple of 10

10000>roundup multiple of 50

50000>roundup Multiple of 100

100000> roundup multiple of 1000

100000<roundup multiple of 10000

Excel Formula:

Philip Treacy

Hi Muhammad,

You’ll need to use the CEILING function for this

Regards

Phil

Brian Jennings

thanks

Chris Mcmahon

Hello,

I am trying to do a rounding up function. i am taking a product length such as a 11′-6″ and trying to round it up to 12′. the biggest issue i am having is it needs to be X by a price for

Example 1; 11′-6″ x $3.76 should = $43.24.

Example 2; 11′-5″ x $3.76 should = $41.36.

can anyone help me on this?

Thanks

Chris

Catalin Bombea

11′-6″ is a text, not a number.

You have to use vba to convert it to decimal inches, to perform calculations on it: try the code from this page: https://social.msdn.microsoft.com/Forums/office/en-US/323edef9-be0d-4f1b-a383-fec5a44be93a/excel-2010-feetinches?forum=olbasics

Chris Mcmahon

I seem to be still having a hard time. the link supplied doesn’t seem to make sense the code it asked me to wright seems to not work.

Catalin Bombea

Hi Chris,

I use that code frequently and works. I copied the code from the link in a workbook for you, and used it for a few conversions, works right out of the box, not sure why you are having trouble using it, make sure you type the apostrophy and double quote from keyboard, if you paste them from browser they are different.

Here is the file from our OneDrive you can test, all set.

Cheers,

Catalin

Penny

Penny says

Hi Andy,

HELP!!

Trying to Creating a spreadsheet for a point system for golf.

I’m try to divide a number in a cell then round down, the number is odd (points need to pull for each 9 hole.

Example: 13/2 = 6.5 in 1cell and 7 in the next cell. Then I putting in points for what they pulled on 1st 9 holes. The answer needs to minus 6 only not the 6.5.

Thank you

Philip Treacy

Hi Penny,

So you want to round 6.5 down to 6?

=ROUNDDOWN(6.5,0)

or generally, if you have a value in A1, the formula would be

=ROUNDDOWN(A1,0)

regards

Phil

Andy

Hi there

I’m putting together a golf handicap spreadsheet where we have 0.25 shot adjustments. The problem is we round down if the handicap is over 28 and round up if under 28.

For example 33.75 = 33 and 19.25 =20.

Is this possible in a formula?

Catalin Bombea

Hi Andy,

Try this formula:

=IF(A1>28,FLOOR(A1,0.25),CEILING(A1,0.25))

Ace

Hi, How do i rounding number such as

0.0 to 0.0

0.1 to 0.0

0.2 to 0.0

0.3 to 0.0

0.4 to 0.0

0.5 to 0.5

0.6 to 1.0

0.7 to 1.0

0.8 to 1.0

0.9 to 1.0

1.0 to 1.0

Apply for negative value also.

Can anyone help? Thanks!

Catalin Bombea

Hi Ace,

Try this formula:

=IF(A1-INT(A1)<0.5,FLOOR(A1,0.5),CEILING(A1,0.5))

Ace

Hi Catalin,

The formula is working, thank you for your help!

Catalin Bombea

You’re wellcome 🙂

Bruce

I am using this formula

=COUNTA(C2:C397)/1965*100&” authentication failure”

The value returned is:

20.1526717557252 authentication failure

How can I round the number in my formula?

Catalin Bombea

Hi Bruce,

This should work, it’s the usual round function:

=Round(COUNTA(C2:C397)/1965*100,2)&” authentication failure”

John

Hello I am creating a spreadsheet with 2 separate formulas to conduct accountability of bottles used to the nearest .25 of the bottle. Formula #1 is {=((E5-F4)*(H4*2))/100}. I have the following answers using this formula [1.19; 1.03; 4.36]. The answers I am looking for is “1.25; 1.00; 4.50”. Thank you.

Mynda Treacy

Hi John,

It’s difficult to visualise how this formula {=((E5-F4)*(H4*2))/100} is returning 3 results: [1.19; 1.03; 4.36]

Can you please post your question on our Excel Forum and upload a sample Excel file so we can help you further.

Mynda

Anantha

Roundup/rounddown etc work fine for rounding to xxxx decimal. but how to round absolute figure (no decimsls)? for example how to round 123456 to nearest tens/hundreds/ thousands etc? Do this,…..round(123456,-1) or round(123456,-2) or -3 or -4……..kakris60@yahoo.com

Catalin Bombea

Hi Anantha,

To round to the nearest integer, no decimal places, use 0 : ROUND(123.12,0)

If you don’t want to round, you can remove decimals with INT(123.12)

Joyce

I am working on a percentage increase in cost, so my equation is:

=(C2*4%)+C2

My problem is how do I write this equation, so it rounds down if under $.50 and rounds up if over $.50?

Mynda Treacy

Hi Joyce,

Try this:

Mynda

S. Behen

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

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

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

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

Great help……to gain an understanding of formulas

Catalin Bombea

Hi Jeff,

Glad to hear you like our tutorials 🙂

Cheers

Catalin

Jennifer Carpenter

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

Hi Jennifer,

You can try this formula:

Mynda

Rohit Kumar

It opened a can of worms coz when my problem was solved i jumped too hard and it opened a can of worms.

Tammy

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

Hi Tammy,

You can use teh CEILING and FLOOR functions for this:

https://www.myonlinetraininghub.com/excel-ceiling-and-floor-functions

Mynda

John

Know one seems to understand. I want the answer to a formal to always Round up to a whole number.

Catalin Bombea

Hi John,

Have you tried =ROUNDUP(A1,0) ?

Catalin

Edmund Weil

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

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

Plz tell me the formula when

E.g.. (E6+F6)×113% in round figure

Mynda Treacy

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

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

Hi Mark,

You should try this:

=IF(A1<50000,0,ROUNDDOWN(A1,-2))

Catalin

Sean Doss

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

Hi Sean,

If you use Round on the

sumof 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

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

No, like this:

Paul

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

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

There is an easier method to ROUND to the nearest 5 cents =MROUND(12365.042, 0.05) you get 12365.05

Mynda Treacy

Thanks, Nelson. Great tip.

They (MS) really should have named that ROUNDM so it doesn’t get overlooked!

Mynda

gary

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

You’re welcome, Gary 🙂

Jane

Thank you for this tutorial – just what I needed 🙂 I’m sure i’ll be back for more help !

Jane

Mynda Treacy

You’re welcome, Jane. Be great to see you back here again sometime.

Ellie Perry

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

Hi Ellie,

Use this

Catalin

Ann Hilton

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

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

Hafizullah

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

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

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

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

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

Thanks for your kind words, Abraham 🙂

vicktor schausberger

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

Mynda Treacy

Hi Vicktor,

You can use this formula:

Kind regards,

Mynda.

lalit

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

Hi Lalit,

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

Where A1 contains your value 50.99

Kind regards,

Mynda.

Gary

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

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

Thank you! This was a great help!

sean

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

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

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

Thanks Rick. Glad I could help.

Jolene

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

J