Excel CEILING and FLOOR Functions

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.

Download the file and practice what you learn.

FREE PDF Download
100 Excel Tips & Tricks

Excel Tips & Tricks E-Book
Just enter your details below
* indicates required

We respect your email privacy

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current ye@r *

Comments

  1. Victoria says

    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!

    • Carlo Estopia says

      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

      • Victoria says

        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!

        • Carlo Estopia says

          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

  2. Norman Harker says

    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.