Share via

Whats the difference between Round, Ceiling and Floor?

Anonymous
2012-01-15T15:19:06+00:00

Whats the difference I use round to round the number to amount of digits specified but what do ceiling and floor do that is different?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2012-01-15T15:51:45+00:00

    **1.**The ROUND function rounds a number to a specified number of digits. For example, if cell A1 contains 23.7825, and you want to round that value to two decimal places, you can use the following formula:

    =ROUND(A1, 2) will return 23.78

    2. CEILING Returns number rounded up, away from zero, to the nearest multiple of significance. 

    Syntax

    CEILING(number,significance)

    Number     is the value you want to round.

    Significance     is the multiple to which you want to round.

    For example, if you want to avoid using pennies in your prices and your product is priced at $23.78, use the formula =CEILING(23.78,0.05) to round prices up to the nearest nickel. i.e. it will return 23.80

    3. FLOORReturns number rounded down, towards zero, to the nearest multiple of significance. 

    Syntax

    FLOOR(number,significance)

    Number     is the value you want to round.

    Significance     is the multiple to which you want to round.

    For example, if you want to avoid using pennies in your prices and your product is priced at $23.78, use the formula =FLOOR(23.78,0.05) to round prices up to the nearest nickel. i.e. it will return 23.75

    50+ people found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2012-01-15T15:48:39+00:00

    Hi Officeanswers,

    Basically explained:

    • ROUND rounds down or up, e.g. 1.3 => 1 & 1.5 => 2 (+ROUND(A1,0)
    • FLOOR rounds always down, e.g. 1.61 => 1.6 (=FLOOR(A1,0.1)
    • CEILING rounds always up, eg. 1.61 => 1.7 (=CEILING(A1,0.1)

    Not sure if they exist already in 2007 (usinig 2010), there is now also a ROUNDUP & ROUNDDOWN.

    =ROUNDDOWN(1.61, 1) => 1.6

    HTH

    Wkr,

    JP Ronse

    10+ people found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-07-08T14:20:51+00:00

    May depend on implementation, but most of the implementations might be as follows:

    round(1.4) = 1

    round(1.5) = 2

    round(-1.4) = -1

    round(-1.5) = -2

    floor(1.4) = 1

    floor(1.5) = 1

    floor(-1.4) = -2

    floor(-1.5) = -2

    ceiling(1.4) = 2

    ceiling(1.5) = 2

    ceiling(-1.4) = -1

    ceiling(-1.5) = -1

    As far as I know.

    3 people found this answer helpful.
    0 comments
  2. Anonymous
    2012-10-18T17:45:04+00:00

    John wrote:

    I am looking for a single function that will do EITHER a ROUNDUP or ROUNDOWN based on the value.  (i.e. on a scale from 1-10, if the value is 1-4, I want to round the value down; if value is 5-10, I want to round the value up). Is there such a function that will do this in a single calculation?

    That's the very definition of ROUND.  But your example is ambiguous insofar as it is unclear what digit you are talking about rounding.

    If you want 1.4 in A1 to round to 1 and 1.5 to round to 2, use ROUND(A1,0).

    If you want 14 to round to 10 and 15 to round to 20, use ROUND(A1,-1).

    See the ROUND help page for more details.

    PS:  For future reference, when you have a question, start a new discussion instead of piggybacking an old discussion, much less a discusion marked "answer".

    First, usually no two questions are every identical in all details.  So your specifics might require a completely different direction.

    Second, some helpful people might never see your new "response" because they do not look at discussions marked "answer".

    Finally, since you are not the original poster, you have no way to mark responses to your question as "answer".

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2012-10-18T17:29:14+00:00

    Hello. I am looking for a single function that will do EITHER a ROUNDUP or ROUNDOWN based on the value.  (i.e. on a scale from 1-10, if the value is 1-4, I want to round the value down; if value is 5-10, I want to round the value up). Is there such a function that will do this in a single calculation? Thanks.

    0 comments