Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Establishing a FLOOR and CEILING

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Establishing a FLOOR and CEILING.

Chances are good that you know how to use the primary rounding functions in Excel: ROUND, ROUNDUP, and ROUNDDOWN. There are two other similar functions you can use, as well: FLOOR and CEILING. The FLOOR function allows you to round down (toward zero) while the CEILING function rounds up (away from zero). Unlike other rounding functions, however, FLOOR and CEILING work with multiples of numbers. For instance, consider the following formula:

=FLOOR(27,12)

This formula will return a value of 24. Why? Because 24 is the largest multiple of 12 between 27 and 0. The CEILING function works similarly, as follows:

=CEILING(27,12)

This formula returns 36, since that is the next multiple of 12 away from zero, but larger than 27.

Both FLOOR and CEILING, as you can tell, require two arguments. The first is the number to be "rounded." Actually, this is a misnomer, as there is no real rounding taking place—the number represents the starting point for determining a relationship between the multiple value (the second argument) and zero.

FLOOR and CEILING can come in handy if you are trying to figure out multiples of items. (In this way it is similar to the MROUND function.) For instance, suppose you were running your youth group candy sale, and you could only deliver candy to the kids in full boxes. Each box contained 12 candy bars. Suppose cell B3 contained an order quantity, and cell C3 contained the number of candy bars in each box (in this case, 12). You could place the following formula in cell D3 to return the number of full boxes that should be delivered:

=FLOOR(B3,C3)/C3

If the number of candy bars ordered (in cell B3) is 31, then the value returned by the formula will be 2. (2 boxes at 12 bars each is 24 bars.) Granted, this formula could also be easily constructed using the INT or TRUNC functions (Excel very often provides multiple ways to accomplish the same tasks). You could use the CEILING function in place of the FLOOR function in this example if you wanted to "round up" to the next full box above what is required for the candy order:

=CEILING(B3,C3)/C3

Using the same scenario, this formula would return 3 (3 boxes, or 36 candy bars).

When using FLOOR and CEILING, remember that the sign of the arguments must match. If one argument is positive and the other negative, Excel returns an error value of #NUM!.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2152) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Establishing a FLOOR and CEILING.

Related Tips:

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.