FLOOR Function in Google Sheets

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.

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 32

or rounding to nearest multiple of 5 like below:

FLOOR(32.21, 5) equals 30

Important 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])
  1. value: The number you want to round-down to nearest multiple of factor
  2. [places]: the number to whose multiple the value will be rounded.

Note:

  1. FLOOR Function only accepts numerical input.
  2. Any form of non-numerical input(like “abc”, “xyz” etc) will result in an “#VALUE!” error.
  3. [factor] is an optional input. 1 is it’s default value.
  4. [factor] cannot be a negative number. Doing this will result in a ‘#NUM!’ error.
  5. 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.

Floor Function - Examples

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.

FLOOR Function Examples

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.

FLOOR Function with Negative Numbers

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.

FLOOR Function  Google Sheets - GIF

That’s it on this topic. Keep browsing SheetsInfo for more such useful information 🙂

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

Liked the content?? Then share the word!!!

Rishav

I love Google Sheets and heavily rely upon it on a day to day basis. SheetsInfo is my attempt to share my learning with all of you. Hope you find the articles easy to read and simple to understand.

Leave a Reply