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
Want to work faster and easier in Excel? Install and use Excel add-ins! Read this article to know the best Excel add-ins to use according to us!
Table of Contents:
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.
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.-
Type an equal sign ( = ) in the cell where you want to put your FLOOR result
-
Type FLOOR (can be with large and small letters) and an open bracket sign after =
-
Input the number you want to round down. Then, type a comma sign ( , )
-
Input the multiple to which you want to round down your number
-
Type a close bracket sign
- Press Enter
-
Done!
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.
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.
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.
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
- How many to send to region 1 for each product if the quantity to send must be in the 30 multiple?
- How many to send to region 2 for each product if the quantity to send must be in the 40 multiple?
- 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:
