FLOOR Function belongs to the family of Mathematical functions in Google Sheet used for rounding numbers.
FLOOR Function stands apart from other sister functions like Round/INT/TRUNC etc since it lets you round a number to nearest multiple of a specified Factor. Does sound a bit tricky right? Not anymore once you go through this article.
Lets cover all the necessary dimension to get a complete understanding of FLOOR Function.
Table of Contents
Purpose of FLOOR Function
Floor function rounds a number down to nearest multiple of a specified factor. In simple words you can use Floor for simple rounding-down purposes like the below:
FLOOR(32.21) equals 32or rounding to nearest multiple of 5 like below:
FLOOR(32.21, 5) equals 30Important point to note here is that in both the cases the output is lower than the input value. This is what defines FLOOR Function. It’s sister function, CEILING, has exact same functionalities, just the difference being that it rounds up to a higher number.
Syntax and Parameter Definition
= FLOOR(value, [factor])- value: The number you want to round-down to nearest multiple of factor
- [places]: the number to whose multiple the value will be rounded.
Note:
- FLOOR Function only accepts numerical input.
- Any form of non-numerical input(like “abc”, “xyz” etc) will result in an “#VALUE!” error.
- [factor] is an optional input. 1 is it’s default value.
- [factor] cannot be a negative number. Doing this will result in a ‘#NUM!’ error.
- You can directly add static values or cell references for both value and [factor] input
Expected Output with Examples
FLOOR Function will look for next lowest number from ‘Value’, whose significance is an multiple of factor specified in the formula.
Surely, we need more than just the above statement to understand usage of FLOOR Function. Let’s look some cases to understand better.
Simplest Case – Floor Function
Below is a text book case of how FLOOR Function works.
In the first case, factor is 10 and next lowest number to 253.212 which is a multiple of 10 is 250.
In the third case, the next smaller number to 253.212 which is also a multiple of 2 is 252.
The last three examples, demonstrate as to how floor works with decimals factor. If you look closely it’s quite straight forward actually. The output is merely a number lower than ‘Value’ which is a multiple of supplied ‘Factors’, 0.5,0.2 & 0.1 respectively.
Working with Decimals
In the below example, we are only working with decimal factors and dealing with different level of significant digits.
While the output may vary by the number of significant digits, the baseline principal remains the same. The output is a smaller than Value as expected and is a integer multiple of the ‘Factor’ specified.
Positive and Negative Numbers
In this section lets see how FLOOR reacts to having a negative number instead of positve.
While it may look like the output from FLOOR is a higher number than Value for Negative Value inputs(1 & 3). But that’s simple not the case. As we know, for negative numbers -253.25 < -253.212. & -255<-253.212
Hence, it might look like we are ending up with a higher number but in reality we are not. FLOOR is working, exactly how it is supposed to.
Visual Demonstration of FLOOR
Before we end here is a simple visual demonstration of FLOOR Function.
That’s it on this topic. Keep browsing SheetsInfo for more such useful information 🙂
