How to Use the FLOOR Function in Google Sheets

Google Sheets is one of the best spreadsheet programs. It has some of the most accessible features, especially when compared to Excel. One of these functions is the FLOOR function, which we will cover in detail. Read on to learn how it works and when to use it.

What is the FLOOR function in Google Sheets?

The FLOOR function rounds a value down to the nearest multiple of a specified factor. This means that a value’s FLOOR number is usually less than the exact value or number. We’ll explain it in more detail after looking at the syntax.

FLOOR function syntax

The FLOOR syntax uses the function name plus the value to be rounded and the factor enclosed in parentheses.

=FLOOR(value,factor)

• =FLOOR: is the function name that indicates the type of calculation to be performed.
• Value: is the number being rounded.
• Factor: is the number of which the result must be a multiple.

This is how the Floor function works

The FLOOR function should give you the largest number that is less than or equal to the value of the nearest integer. For example, if you have a value of 1.3 and a factor of 1, then the FLOOR number of that is 1 because one is the largest value to the next 1 fewer as 1.3.

If the value is an integer, its FLOOR is equal to it. For example, if the value is 7 and we specify a factor of 1, its FLOOR is 7. This is because 7 is still the nearest whole number.

It is important to note that the FLOOR does this Not round down to a value that is not a multiple of the factors. For example, if we specified 8 as the value and 3 as the factor, the result would be 6 because it is the nearest rounded multiple of 3.

Examples of the FLOOR function in Google Sheets

Now that we’re clear on what the FLOOR function is and how it works, let’s take a look at some examples. While you can use written numbers inside the FLOOR function, you will most likely be using cell references. You can use the following steps as a general guide to using the floor function with cell references:

A quick breakdown

1. Enter your values ​​in the cells.
2. Enter your factors in the adjacent cells.
3. In the blank cell next to the factor, enter the formula =FLOOR(value,factor) where value is the cell position of the value and factor is either the cell position of the factor or the factor itself.
4. Press Enter to get the results.
5. Use the fill handle or the Google Sheets autofill suggestion to fill in the remaining cells in the column.

Example 1: Using cell references with the FLOOR function

Here’s an example of using FLOOR in Google Sheets with a table of values:

1. Open a new Google Sheets document.
2. Enter the values ​​and factors you want to use for FLOOR. Be sure to label them clearly.
3. Click where you want the first result to appear. That is C4 in our example.
4. write the equal sign (=).
5. Type Flo Wait for the options to appear and select GROUND. Note that Sheets tells you the syntax of the function when you select it this way.

6. Click the cell that contains the value to round. The cell name should appear within the brackets in the formula.

7. Enter a comma.
8. Click on the cell with the factor.

9. Press Enter, and the result is displayed.

As you can see, the FLOOR has increased from 38.5 to a factor of 3 36. This means that 36 is the closest number to 38.5, is less than 38.5, and is a multiple of 3.

Usually a suggestion box will appear with the option to autofill the rest of the table. Click the checkmark to autofill the table.

Example 2: Using numbers instead of cell references

You don’t have to use the cell to enter the formulas, you can also use the values ​​directly, for example: if we were to use the same table of values ​​again, in cell C4the formula:

 =FLOOR(A4,B4)

Can be replaced by:

 =FLOOR(38.5,3)

When you press Enter, it will give you the result. However, it does not allow you to auto-populate the table. You also can’t use the fill handle because it just applies the same numbers to the function and not to the others from the table. So if you enter the numbers instead of using cell references, you’ll have to fill in each result individually.

Explanation of the results

If we look at row 4, the value given is 38.5. The factor is 3, so the FLOOR syntax calculates the number closest to 38.5, which is a multiple of 3 and is less than 38.5, which gives a result of 36.

The factor can also be specified in decimal places, for example: In line 6 the factor is 0.1. The function now tries to round down to the nearest tenth instead of a whole number. Therefore, the results have a decimal value in tenths. In the example above we have 12.2.

There are times when you have a negative number as a value. Although the results appear to be increasing, negative numbers usually decrease the higher they go.

In the example below, we changed the value in the cell A8 from 6.7 to -6.7. The result of the FLOOR function will be -8th. This is because -8 is the next lower number at -6.7, which is also a multiple of 2.

When to use the FLOOR function in Google Sheets

You can use FLOOR functions to round off numbers when dealing with things like currencies, such as B. Rounding foreign currency exchange rates. Sometimes there are too many decimal places after currency conversion.

For example, you can use the FLOOR function when converting US dollars to euros to round down to the nearest 1, 0.1, 0.01, or any other decimal place you want. You can also do this using the formatting menu in Google Sheets by going to Format > Number > Custom Currency.

You can also nest the FLOOR function with others to perform complex calculations. But that’s better covered in our guide to using Google Sheets like a pro.

Master the FLOOR function in Google Sheets

This guide took an in-depth look at the basics of the FLOOR function in Google Sheets. However, this is just the tip of the iceberg when looking at what this powerful spreadsheet program can do. Keep practicing to master the FLOOR function and every aspect of Google Sheets.