• 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
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • 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

Excel CEILING and FLOOR Functions

You are here: Home / Excel Formulas / Excel CEILING and FLOOR Functions
May 11, 2011 by Mynda Treacy

In Australia we did away with 1 and 2 cent coins years ago. So all prices, where paid in cash, are rounded down to the nearest value divisible by 5 cents.

In Excel we can use the FLOOR function to calculate this value. For example:

Say our price is $4.32 and we need to round it down to the nearest value divisible by 5 cents, the FLOOR function would read:

=FLOOR(4.32, 0.05)

= $4.30
 
Excel FLOOR function example

On the flip side we could use the CEILING function to round our price up to the nearest 5 cents as follows:

=CEILING(4.32, 0.05)

= $4.35
 
Excel CEILING function example

How do the FLOOR and CEILING Functions Work?

The syntax is:

=FLOOR(number, significance)
=CEILING(number, significance)

Where the number is your starting point and the significance is the multiple you want your number rounded down to for FLOOR, or up to for CEILING.

Unlike ROUNDUP or ROUNDDOWN, Excel’s FLOOR and CEILING functions can round the decimal places of a value to be divisible by a number you specify.

FLOOR Function Cool Trick

You’ve probably noticed that ‘7’ is the new ‘9’; with prices like $9.97 now in place of $9.99, and $9.47 in place of $9.49.  So what if you wanted to round your prices to end in 47 cents or 97 cents?

We can use a combination of the FLOOR function and an IF statement to achieve this.

In row 25 in the example below you can see in the formula bar we have used the following formula:

=FLOOR(D25,1)+IF(CEILING(D25,1)-(D25)<0.5,0.97,0.47)

 
Excel FLOOR function trick
 
Taking row 25 as an example let me break this formula down and explain it in English:

1.       The first part

 =FLOOR(D25,1)

simply gets rid of the decimal places so you’re left with $4.00.

2.       The second part

IF(CEILING(D25,1)-(D25)<0.5,0.97,0.47)

works out if the cents are less than 50, and if they are round them down to 47 cents, otherwise round them up to 97 cents.

3.       The plus sign in between the first FLOOR function and the IF Statement adds the two results together giving the result $4.47.

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 file and practice what you learn. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.

More Math & Trig Posts

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.

How to Round Numbers in Excel Using 3 Rounding Functions

Use ROUND, ROUNDUP and ROUNDDOWN functions to round your numbers in Excel. Lots of examples and a sample workbook to download

More Excel Formulas Posts

excel formula by example

Excel Formula by Example

Excel can now write a formula by example. Simply give it an example or two of the result and Excel will write the formula.
ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
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.


Category: Excel FormulasTag: math & trig
Previous Post:Excel CHOOSE FunctionExcel CHOOSE Function
Next Post:Excel Tips for Building Workbooks FASTExcel Tips for Building Workbooks FAST

Reader Interactions

Comments

  1. Daniel

    March 2, 2023 at 2:03 am

    Hello Mynda

    Thank you very much for this VERY useful tutorial and thank you to all the responders with their unique scenarios and solutions.

    I was encountering the problem with your original formula of items priced $2.09 would round up to 2.49 instead of 1.99 which we prefer in our store to be more competitive. I ended up using @Siôn formula to help facilitate and it is successfully in those instances where items are priced closer to 1.99.

    Although for items at 7.34, I need to use @Mynda formula to get to 7.49.

    Do you know how I could combine your 2 formulas?

    Thank you Mynda and Siôn

    Reply
    • Mynda Treacy

      March 2, 2023 at 9:35 am

      Hi Daniel,

      It sounds like you have some special rules like the 7.34 rounding to 7.49 etc. Please post your question on our Excel forum where you can also upload a sample file showing all scenarios and your desired result and we can help you further.

      Mynda

      Reply
  2. vinod

    October 21, 2019 at 3:35 pm

    hi, please help me, IN excel i want both if 17.4 it should be round to 17, and if it is 17.6 it should become 18, i want single formula

    Reply
    • Catalin Bombea

      October 21, 2019 at 9:49 pm

      Try:
      =Round(A1,0)

      Reply
  3. Jayasree

    July 18, 2017 at 7:38 pm

    in an excel round off at a rate of 50 paise is less than fifty paise below zero (eg.Rs.2147.41 is round off Rs.2147 & Rs.2147.52 is round off Rs.2148)
    which formula apply this for an excel

    pls help me

    thanks

    Reply
    • Catalin Bombea

      July 20, 2017 at 3:43 am

      Hi Jayasree,
      Use a simple Round function, to 0 decimal places:
      =Round(A1,0)

      Reply
  4. Charles

    April 8, 2017 at 3:31 pm

    In your cool trick, couldn’t you just use the following?
    =CEILING(D25,0.5)-0.03

    Reply
    • Mynda Treacy

      April 8, 2017 at 4:30 pm

      Not quite, Charles. If the value in D25 is 4.32 your formula still returns 4.32.

      Reply
      • David N

        August 4, 2017 at 4:12 am

        I agree with Charles. CEILING(4.32,0.5) = 4.50, and 4.50-0.03 = 4.47. So I don’t see how his formula isn’t perfect. Were you perhaps thinking of CEILING(4.32,0.05) instead — i.e. rounding up to the nickel instead of the half dollar?

        Reply
        • Mynda Treacy

          August 4, 2017 at 11:19 am

          Hi David,

          I assumed Charles was referring to the rounding to .47 or .97 scenario, but his formula didn’t solve either. =CEILING(4.32,0.5) = 4.35, not 4.50.

          Mynda

          Reply
          • David N

            August 5, 2017 at 12:05 am

            Is it possible there is some regional setting on your computer that is creating the difference because that formula absolutely resolves to 4.50 using both CEILING and CEILING.MATH.

            $4.32 $4.50 =CEILING.MATH(A1,0.5)
            $4.49 $4.50 =CEILING.MATH(A2,0.5)
            $4.50 $4.50 =CEILING.MATH(A3,0.5)
            $4.51 $5.00 =CEILING.MATH(A4,0.5)

            Versus…

            $4.32 $4.35 =CEILING.MATH(A6,0.05)
            $4.49 $4.50 =CEILING.MATH(A7,0.05)
            $4.50 $4.50 =CEILING.MATH(A8,0.05)
            $4.51 $4.55 =CEILING.MATH(A9,0.05)

            Therefore…

            $4.32 $4.47 =CEILING.MATH(A11,0.5)-0.03

          • Mynda Treacy

            August 5, 2017 at 2:06 pm

            Hi David,

            Not that I’m aware of. The example on Microsoft’s website also says 4.42 will round to the nearest nickel.

            Do you want to share your file on our Excel Forum and include a screenshot in the file of how it appears to you so I can test it on my PC?

            Mynda

          • David N

            August 16, 2017 at 6:15 am

            I don’t think posting a file or screen shots will be necessary because the example from Microsoft is using this formula: =CEILING(4.42,0.05). Note that’s zero point zero five — i.e. five cents or one nickel. Whereas Charles and I have been proposing =CEILING(4.42,0.5), which is zero point five or zero point five zero if you prefer — i.e. fifty cents or half a dollar. And that’s the critical difference — 0.05 versus 0.50. So our formula rounds to the nearest half dollar and then subtracts three cents so that everything rounds to 47 or 97.

          • Mynda Treacy

            August 16, 2017 at 12:35 pm

            Right you are, David. It appears I could not see the ‘0’ for the trees! 🙂

  5. Siôn

    February 23, 2017 at 11:26 pm

    Hi there, and thank you for this great material.
    Regarding the cool trick, maybe not so cool but shorter using ROUND:
    =IF(D25<0.5,0.47,ROUND(D25,0)-0.03).
    What do you think?

    Reply
    • Mynda Treacy

      February 24, 2017 at 12:38 pm

      Hi Siôn,

      Thanks for sharing your formula. I get $3.97 for row 25 so I think you need to build in some more logic to handle values that end in the range .01 to .49.

      Mynda

      Reply
  6. jonny

    September 25, 2014 at 12:22 pm

    That is great tips. I may change your formula to remove ‘CEILING’ part. See below,
    =FLOOR(D25,1)+IF((D25-FLOOR(D25,1))<0.5, 0.47, 0.97). What do you think?
    Thanks,
    Jon

    Reply
    • Mynda Treacy

      September 25, 2014 at 6:38 pm

      Thanks, Jon. Swings and roundabouts I think 🙂

      Cheers,

      Mynda

      Reply
  7. Victoria

    January 23, 2013 at 3:32 am

    I am looking for a way to round up to the nearest .05. If the price already ends in a 0 or 5, I would like it to remain and not round up. I can’t seem to figure out how to do this consistently. It seems the price will stay in some instances and in others, it rounds up. Any tips? Thanks!

    Reply
    • Carlo Estopia

      January 23, 2013 at 5:25 pm

      Hi Victoria,

      This is the quickest I could get back to you:

      =IF(OR(A1-INT(A1) = 0,A1-INT(A1) >0.05),A1,CEILING(A1,0.05))

      There may be other workarounds.

      Here’s the Pseudo-Formula

      =IF Decimal is is 0 or Greater than .05 then
      Decimal stays the same.
      for i.e. 4.00 = 4.00
      4.06 = 4.06
      4.056 = 4.056
      IF Decimal is .01 to .05 then
      it’s rounded up to .05
      for i.e. 4.02 = 4.05

      Now this may not be what you desire So I would gladly appreciate any further clarification.

      Read More on Ceiling and Floor Functions
      Rounding Off Numbers

      Thanks.

      CarloE

      Reply
      • Victoria

        January 24, 2013 at 8:23 am

        Thank you, I appreciate the response.

        This did help. However, not in all cases.

        If the decimal is greater than .05 then I want it to round up. I have listed examples of how I would like the rounding to go below. Basically, I have a set of prices that are derived from a separate formula. I want these prices to all stay where they are if they end in a 5 or zero. But, if they do not I would like them to round up.

        3.60 = 3.60
        3.65 = 3.65
        3.67 = 3.70
        3.61 = 3.65

        I have tried to edit the formula you so kindly sent, but I am not having any success. I would appreciate any further guidance you could provide. Thanks!

        Reply
        • Carlo Estopia

          January 24, 2013 at 1:37 pm

          Hi Victoria,

          Sorry for that. Anyway now that it is clear… here it is:

          =IF(INT(RIGHT(MOD(A1,1),1))<=5,ROUNDDOWN(A1,1)+0.05,ROUNDUP(A1,1)) or =IF(INT(RIGHT(A1-INT(A1),1))<=5,ROUNDDOWN(A1,1)+0.05,ROUNDUP(A1,1)) The Formula is simple: If the decimal tenths is a 5 or less then stays 5 else rounded up to the nearest decimal ones or rounded up to zero i.e. .05 is to .10 Note: Mod extracts the decimals out of a given number Right isolates the tenths place which is 5 Rounddown rounds down to the next lowest decimal ones + .05 RoundUp rounds the number up to the nearest decimal ones Sincerely, CarloE

          Reply
  8. Norman Harker

    January 2, 2013 at 6:50 am

    Your “neat trick” could cause major damage to legal health! See S18 Consumer and Competition Act 2010.

    Your column B quotes a discount.

    With Oranges, Apples, and Grapes the rounded price is now LESS than the discounted price.

    If you publicise your discount, your rounded price in those three cases will be misleading or deceptive conduct unless you use “Up to 10% off” system.

    Reply
    • Mynda Treacy

      January 2, 2013 at 8:39 am

      😀 Funny!

      Thanks, Norman.

      Reply

Trackbacks

  1. Excel Formulas - My Online Training Hub says:
    September 9, 2014 at 10:08 pm

    […] FLOOR and CEILING Functions […]

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

launch excel macros course excel vba course

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

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

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

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.

Blog Categories

  • 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
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

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