• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

How to Round Numbers in Excel Using 3 Rounding Functions

You are here: Home / Excel Formulas / How to Round Numbers in Excel Using 3 Rounding Functions
November 8, 2010 by Mynda Treacy

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.

rounding error

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.

Excel ROUND formulas using the round function

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.

ROUND to a 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 functions to do this.

Enter your email address below to download the sample workbook.



By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the workbook and practice what you've learnt. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.

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.

More Math & Trig Posts

Excel CEILING and FLOOR Functions

Excel CEILING and FLOOR Functions allow you to round values up or down to the nearest value divisible by a specified number. e.g ending in 95 cents.
Excel RAND and RANDBETWEEN Functions

Excel RAND and RANDBETWEEN Functions

Excel RAND and RANDBETWEEN Functions enable you to generate a list of random values either decimals or integers, based on upper and lower bounds.

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.
Category: Excel FormulasTag: math & trig
Previous Post:Top Tips to Avoid SpamTop Tips to Avoid Spam
Next Post:6 Features of Microsoft Office That You Should Know About and Use

Reader Interactions

Comments

  1. Andy

    November 22, 2022 at 8:03 am

    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

    Reply
    • Mynda Treacy

      November 22, 2022 at 9:56 am

      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.

      Reply
  2. Sue Hill

    April 17, 2020 at 7:28 am

    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)

    Reply
    • Mynda Treacy

      April 17, 2020 at 9:31 am

      Hi Sue,

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

      Mynda

      Reply
  3. Craig

    February 18, 2020 at 12:00 am

    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

    Reply
    • Catalin Bombea

      February 18, 2020 at 1:18 pm

      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)

      Reply
  4. Manfred Granig

    September 5, 2019 at 10:22 am

    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?

    Reply
    • Catalin Bombea

      September 5, 2019 at 2:50 pm

      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.

      Reply
      • Manfred Granig

        September 5, 2019 at 4:33 pm

        Thanks that’s awesome – works great!!!

        Reply
  5. simon

    July 10, 2019 at 12:15 am

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

    Reply
    • Catalin Bombea

      July 10, 2019 at 1:11 pm

      You’re welcome, glad to help.

      Reply
  6. deepak

    May 7, 2019 at 3:10 pm

    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

    Reply
    • Catalin Bombea

      May 8, 2019 at 12:17 am

      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.

      Reply
  7. P Ross

    March 29, 2019 at 1:31 am

    $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

    Reply
    • Philip Treacy

      March 29, 2019 at 3:26 pm

      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

      Reply
  8. Future's Genius

    March 14, 2019 at 8:38 pm

    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,

    Reply
    • Mynda Treacy

      March 15, 2019 at 9:29 am

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

      Reply
  9. ASHOK KOUSHAL

    October 31, 2018 at 10:02 pm

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

    Reply
    • Philip Treacy

      November 1, 2018 at 9:57 am

      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

      Reply
  10. Matt Frandock

    July 17, 2018 at 9:26 am

    How would I make this formula roundup

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

    Reply
    • Mynda Treacy

      July 17, 2018 at 9:33 am

      Hi Matt,

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

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

      Mynda

      Reply
  11. Rachel

    July 6, 2018 at 9:10 am

    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.

    Reply
    • Catalin Bombea

      July 7, 2018 at 3:47 pm

      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

      Reply
  12. Lloyd

    June 30, 2018 at 1:21 am

    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

    Reply
    • Mynda Treacy

      July 3, 2018 at 4:01 am

      Thanks, Lloyd! Glad we could help 🙂

      Reply
  13. Brian Jennings

    May 17, 2018 at 5:11 am

    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)

    Reply
    • Philip Treacy

      May 17, 2018 at 10:18 am

      Hi Brian,

      You need to use an IF function too

      =IF(A1>=1000,ROUNDUP(A1,1),ROUNDDOWN(A1,1))
      

      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

      Reply
  14. Muhammad Zohair

    May 15, 2018 at 9:42 pm

    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:

    =IF(I6<=10,MROUND(I6,1),IF(I6<=100,MROUND(I6,5),IF(I6<=1000,MROUND(I6,10),IF(I6<=10000,MROUND(I6,50),IF(I6I6,MROUND(I6,1000),IF(100000<=I6,MROUND(I6,10000))))
    
    Reply
    • Philip Treacy

      May 17, 2018 at 11:10 am

      Hi Muhammad,

      You’ll need to use the CEILING function for this

      =IF(I6<=10,ROUNDUP(I6,0),IF(I6<=100,CEILING(I6,5),IF(I6<=1000,CEILING(I6,10),IF(I6<=10000,CEILING(I6,50),IF(I6<=50000,CEILING(I6,100),IF(I6<=100000,CEILING(I6,1000),IF(I6<=1000000,CEILING(I6,10000),0)))))))
      

      Regards

      Phil

      Reply
      • Brian Jennings

        May 22, 2018 at 5:21 am

        thanks

        Reply
  15. Chris Mcmahon

    April 27, 2018 at 6:10 am

    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

    Reply
    • Catalin Bombea

      April 27, 2018 at 5:04 pm

      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

      Reply
      • Chris Mcmahon

        April 28, 2018 at 5:13 am

        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.

        Reply
        • Catalin Bombea

          April 29, 2018 at 1:57 pm

          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

          Reply
  16. Penny

    April 4, 2018 at 10:25 pm

    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

    Reply
    • Philip Treacy

      April 5, 2018 at 11:19 am

      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

      Reply
  17. Andy

    February 28, 2018 at 3:50 am

    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?

    Reply
    • Catalin Bombea

      February 28, 2018 at 4:07 pm

      Hi Andy,
      Try this formula:
      =IF(A1>28,FLOOR(A1,0.25),CEILING(A1,0.25))

      Reply
  18. Ace

    January 8, 2018 at 7:24 pm

    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!

    Reply
    • Catalin Bombea

      January 8, 2018 at 11:17 pm

      Hi Ace,
      Try this formula:
      =IF(A1-INT(A1)<0.5,FLOOR(A1,0.5),CEILING(A1,0.5))

      Reply
      • Ace

        January 9, 2018 at 11:19 am

        Hi Catalin,

        The formula is working, thank you for your help!

        Reply
        • Catalin Bombea

          January 9, 2018 at 3:31 pm

          You’re wellcome 🙂

          Reply
  19. Bruce

    December 7, 2017 at 3:14 am

    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?

    Reply
    • Catalin Bombea

      December 7, 2017 at 3:29 pm

      Hi Bruce,
      This should work, it’s the usual round function:
      =Round(COUNTA(C2:C397)/1965*100,2)&” authentication failure”

      Reply
  20. John

    November 4, 2017 at 12:07 am

    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.

    Reply
    • Mynda Treacy

      November 5, 2017 at 9:03 pm

      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

      Reply
  21. Anantha

    October 22, 2017 at 9:49 pm

    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

    Reply
    • Catalin Bombea

      October 23, 2017 at 2:17 am

      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)

      Reply
  22. Joyce

    October 14, 2017 at 3:01 am

    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?

    Reply
    • Mynda Treacy

      October 14, 2017 at 1:57 pm

      Hi Joyce,

      Try this:

      =INT(CEILING((C2*4%)+C2,0.5))

      Mynda

      Reply
  23. S. Behen

    August 15, 2017 at 10:53 am

    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

    Reply
    • Catalin Bombea

      August 15, 2017 at 2:57 pm

      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))))

      Reply
  24. Suhail Amin Bhat

    July 29, 2017 at 10:01 am

    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

    Reply
    • Mynda Treacy

      July 29, 2017 at 4:21 pm

      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?

      =INT(SUM(A1:A4))

      Mynda

      Reply
  25. Jeff

    June 17, 2017 at 8:35 am

    Great help……to gain an understanding of formulas

    Reply
    • Catalin Bombea

      June 17, 2017 at 2:57 pm

      Hi Jeff,
      Glad to hear you like our tutorials 🙂
      Cheers
      Catalin

      Reply
  26. Jennifer Carpenter

    February 4, 2017 at 9:15 am

    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”,”-“)

    Reply
    • Mynda Treacy

      February 4, 2017 at 1:25 pm

      Hi Jennifer,

      You can try this formula:

      =IF(ABS(T3-O3)<1,"check","-")

      Mynda

      Reply
  27. Rohit Kumar

    November 3, 2016 at 9:10 pm

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

    Reply
  28. Tammy

    October 18, 2016 at 8:09 am

    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.

    Reply
    • Mynda Treacy

      October 18, 2016 at 11:32 am

      Hi Tammy,

      You can use teh CEILING and FLOOR functions for this:

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

      Mynda

      Reply
  29. John

    April 14, 2016 at 2:26 am

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

    Reply
    • Catalin Bombea

      April 14, 2016 at 5:34 am

      Hi John,
      Have you tried =ROUNDUP(A1,0) ?
      Catalin

      Reply
  30. Edmund Weil

    March 25, 2016 at 1:23 am

    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

    Reply
    • Catalin Bombea

      March 25, 2016 at 4:24 pm

      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

      Reply
  31. Harry

    February 6, 2016 at 1:16 pm

    Plz tell me the formula when

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

    Reply
    • Mynda Treacy

      February 6, 2016 at 6:31 pm

      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

      Reply
  32. Mark Lewis

    December 9, 2015 at 2:02 am

    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?

    Reply
    • Catalin Bombea

      December 9, 2015 at 2:59 pm

      Hi Mark,
      You should try this:
      =IF(A1<50000,0,ROUNDDOWN(A1,-2))
      Catalin

      Reply
  33. Sean Doss

    August 7, 2015 at 2:13 am

    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

    Reply
    • Catalin Bombea

      August 7, 2015 at 4:23 am

      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

      Reply
  34. paul

    July 14, 2015 at 9:00 pm

    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

    Reply
    • Mynda Treacy

      July 14, 2015 at 9:05 pm

      No, like this:

      =ROUNDDOWN((D13-(D13*E10))/0.05,0)*0.05
      
      Mynda
      Reply
  35. Paul

    July 14, 2015 at 7:58 pm

    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!

    Reply
    • Mynda Treacy

      July 14, 2015 at 8:17 pm

      Hi Paul,

      You can use this formula:

      =ROUNDDOWN((D$2*C13)*52/12/0.05,0)*0.05

      No need for SUM unless you want to SUM a range of cells.

      Kind regards,

      Mynda

      Reply
  36. Nelson Ndigi

    January 24, 2015 at 6:41 pm

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

    Reply
    • Mynda Treacy

      January 24, 2015 at 10:46 pm

      Thanks, Nelson. Great tip.

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

      Mynda

      Reply
  37. gary

    January 14, 2015 at 3:05 pm

    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

    Reply
    • Mynda Treacy

      January 14, 2015 at 8:12 pm

      You’re welcome, Gary 🙂

      Reply
  38. Jane

    August 19, 2014 at 9:18 pm

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

    Jane

    Reply
    • Mynda Treacy

      August 20, 2014 at 7:10 am

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

      Reply
  39. Ellie Perry

    June 11, 2014 at 5:34 am

    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.

    Reply
    • Catalin Bombea

      June 11, 2014 at 4:11 pm

      Hi Ellie,
      Use this

      =Ceiling(A1*0.6,0.25)

      Catalin

      Reply
  40. 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
  41. 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
  42. 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
  43. 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
  44. 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
  45. 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
  46. 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
  47. Scott

    October 26, 2011 at 11:01 pm

    Thank you! This was a great help!

    Reply
  48. 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
  49. 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
  50. 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

Trackbacks

  1. ROUNDUP and ROUNDDOWN with a Twist • My Online Training Hub says:
    September 14, 2014 at 12:09 pm

    […] the formula looks for the last value in the cell. So you either have to start with whole numbers or ROUND them to no decimal […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x