How to Use Excel FLOOR Function: Usabilities, Examples, and Writing Steps - Compute Expert

How to Use Excel FLOOR Function: Usabilities, Examples, and Writing Steps


Home >> Excel Tutorials from Compute Expert >> Excel Formulas List >> How to Use Excel FLOOR Function: Usabilities, Examples, and Writing Steps





In this tutorial, you will learn how to use the excel FLOOR function completely. We will discuss its usabilities, inputs, example, writing steps, and use cases here.

When we work with numbers in excel, we may sometimes need to round them down to a certain multiple. FLOOR can help you with that rounding process so you can get the result easily.

Want to know more about FLOOR and the way to use it optimally in excel? Read this tutorial until its last part!

Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more







What is the FLOOR Function in Excel?

FLOOR is an excel function that can help us in a number rounding down process to a certain multiple.



FLOOR Usabilities

FLOOR rounds down our number to a certain multiple that we determine ourselves.



FLOOR Result

The FLOOR result is a number which is our number rounding down result to a certain multiple.



Excel Version from Which We Can Use FLOOR

We can start using FLOOR since excel 2003.



The Way to Write It and Its Inputs

Here is the general writing form of FLOOR in excel.

= FLOOR ( number_to_round , multiple )


And here is the explanation of each input we have to give in our FLOOR writing.
  • number_to_round = the number we want to round down to a certain multiple
  • multiple = the multiple to where we want to round our number



Example of Its Usage and Result

For a better understanding of the formula, here is an implementation example of FLOOR in excel.

How to Use Excel FLOOR Function: Usabilities, Examples, and Writing Steps - Screenshot of the FLOOR Implementation Example

As you can see there, the writing of FLOOR is quite easy. Just input the number we want to round down and the multiple that we base our rounding process on. As a result of that, we get our number rounding result!



If we input negative numbers as our FLOOR inputs, then FLOOR will round our number closer to zero.



Writing Steps

After seeing the FLOOR writing form, inputs explanation, and implementation example, now let’s discuss its writing steps. As FLOOR only needs two easy-to-understand inputs, you should be able to follow these writing steps easily.

  1. Type an equal sign ( = ) in the cell where you want to put your FLOOR result

    How to Use Excel FLOOR Function: Usabilities, Examples, and Writing Steps - Screenshot of Step 1

  2. Type FLOOR (can be with large and small letters) and an open bracket sign after =

    How to Use Excel FLOOR Function: Usabilities, Examples, and Writing Steps - Screenshot of Step 2

  3. Input the number you want to round down. Then, type a comma sign ( , )

    How to Use Excel FLOOR Function: Usabilities, Examples, and Writing Steps - Screenshot of Step 3

  4. Input the multiple to which you want to round down your number

    How to Use Excel FLOOR Function: Usabilities, Examples, and Writing Steps - Screenshot of Step 4

  5. Type a close bracket sign

    How to Use Excel FLOOR Function: Usabilities, Examples, and Writing Steps - Screenshot of Step 5

  6. Press Enter
  7. Done!

    How to Use Excel FLOOR Function: Usabilities, Examples, and Writing Steps - Screenshot of Step 7




FLOOR to Round Down Date

Since a date is a number data type too, you can use FLOOR to round a date down to days multiple. Just input the date you want to round and the days multiple for the rounding process basis to FLOOR.

Here is the FLOOR general writing form in excel to round a date.

= FLOOR ( date_to_round , days_multiple )


And here is the implementation example of the concept.

How to Use Excel FLOOR Function: Usabilities, Examples, and Writing Steps - Screenshot of the FLOOR Implementation Example to Round Down Date

If you want to round your date to a certain days multiple, remember to use FLOOR!



FLOOR to Round Down Time

You can also round down time to certain hours, minutes, or seconds multiple using FLOOR. To do that, you need to give your FLOOR multiple input in the form of time data.



Here is the general writing form of FLOOR to round down time in excel.

= FLOOR ( time_to_round , time_multiple )


And here is the concept implementation example in excel.

How to Use Excel FLOOR Function: Usabilities, Examples, and Writing Steps - Screenshot of the FLOOR Implementation Example to Round Down Time

In the example, we round our time data to 1 hour, 15 minutes, and 20 minutes multiple using FLOOR. As you can see there, we input those multiples in the form of time too. As a result, we get the time rounding results that we want!



FLOOR Alternative: FLOOR.MATH

Since excel 2013, we can use FLOOR.MATH as an alternative to FLOOR. Just like FLOOR, FLOOR.MATH also rounds down a number to a certain multiple. The differences between the two are the default multiple input and the negative number rounding process control in FLOOR.MATH.

If we use FLOOR, then we have to input a multiple as the basis of its rounding down process. FLOOR will also round a negative number closer to zero and there seems nothing that we can do about that.

If we use FLOOR.MATH, however, then we have a default multiple input of 1. That means if we don’t input any multiple, then FLOOR.MATH will round our number down to the nearest 1 multiple.

We also have more control when we round a negative number using FLOOR.MATH. We can determine whether we want to round it closer to zero or away from zero.

Give 0 as the FLOOR.MATH third input or don’t give any input to round a negative number away from zero. To round a negative number closer to zero, input any non-zero value as your FLOOR.MATH third input.

If we round a positive number, then this third input of FLOOR.MATH won’t affect anything.

To summarize, here is the general writing form of FLOOR.MATH in excel.

= FLOOR.MATH ( number_to_round , [ multiple ] , [ negative_number_rounding_mode ] )


And here is its implementation example.

How to Use Excel FLOOR Function: Usabilities, Examples, and Writing Steps - Screenshot of the FLOOR.MATH Implementation Example

As you can see, when we round a positive number, FLOOR and FLOOR.MATH function the same. However, we can give no multiple to FLOOR.MATH and it will still round down our number to the 1 multiple.

We can also control how we round our negative number using FLOOR.MATH. If you need to round a negative number closer to zero, input a non-zero value as its third input. Otherwise, just don’t give any third input to your FLOOR.MATH.

If you need the differences that FLOOR.MATH can give when rounding down your number, then use it as your formula



Exercise

After you have understood how to use FLOOR in excel, let’s sharpen your understanding more by doing the exercise below!

Download the exercise file and answer all the questions. Download the answer key file to check your answers after you have done the exercise. Or probably when you are confused about how to answer the questions!

Link to the exercise file:
Download here

Questions

  1. How many to send to region 1 for each product if the quantity to send must be in the 30 multiple?
  2. How many to send to region 2 for each product if the quantity to send must be in the 40 multiple?
  3. How many to send to region 3 for each product if the quantity to send must be in the 50 multiple?

Link to the answer key file:
Download here



Additional Note

If you round a positive number to a negative multiple in FLOOR, you will get a #NUM error.



Related tutorials you should learn from:



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!





Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!




Follow our tutorial content also on


Hand-Picked CE Tutorials

Excel Calculation

How to Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division Excel Calculation

Average Excel Calculation



Excel Formula

VLOOKUP Excel Formula

Excel IF Function

SUM Formula in Excel

COUNTIF Formula in Excel

COUNT Function in Excel



Excel Tips and Trick

How to Print in Excel

Convert Number to Text Excel

Excel Worksheet Definition

Excel Range Definition

How to Add Columns in Excel



Excel Consultation

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition



© 2021 Compute Expert