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 functions, especially when compared to Excel. One of these functions is the FLOOR function. Read on to learn how and when to use the FLOOR function in Google Sheets.

What is the FLOOR function in Google Sheets?

The FLOOR function rounds the value of a specified factor down to the nearest multiple. This means that the FLOOR of a value will often be less than the exact value or number. The article will explain in more detail after reviewing the syntax.

FLOOR . function syntax

The FLOOR syntax uses the function name plus the rounded value and the coefficient enclosed in brackets.

=FLOOR(value,factor)

1. =FLOOR is the function name that specifies the type of calculation to perform.
2. value is the number to be rounded.
3. factor is the number whose result must be a multiple of it.

How the FLOOR . function works

The FLOOR function will give you the largest number that is less than or equal to the value of the nearest integer. For example, if you have the value 1.3 and the coefficient is 1, the FLOOR of this value is 1 because one is a large value and the closest to 1 is less than 1.3.

If the value is an integer, its FLOOR will be equal to itself. For example, if the value is 7 and the specified coefficient is 1, its FLOOR will be 7. This happens because 7 is also the closest integer to itself.

It is important to note that FLOOR does not round down to values ​​that are not multiples of factors. For example, if you specify 8 as the value and 3 as the coefficient, the result will be 6 because it is the nearest round down multiple of 3.

Example of FLOOR function in Google Sheets

Now that you are clear about what the FLOOR function is and how it works, let's take a look at some examples. Although you can use numbers written inside the FLOOR function, you will most likely use cell references. You can use the following steps as a general guide to using the FLOOR function with cell references:

Quick analysis

1. Enter your values ​​in the boxes.

2. Enter your elements in the boxes next to them.

3. On the empty cell next to the factor, put the formula =FLOOR(value,factor) where value is the cell position of the value and factor is the cell position of the factor or the element itself.

4. Press Enter to get the result.

5. Use the fill handle or the autofill suggestion in Google Sheets to fill in the remaining cells in the column.

Example 1: Using cell references with the FLOOR . function

Here's an example of how to use FLOOR in Google Sheets with a table of values:

How to use the FLOOR function in Google Sheets Picture 1

1. Open a new Google Sheets document.

2. Enter the values ​​and factors you want to apply the FLOOR function to. Make sure to label them clearly.

3. Click where the first result will appear. This is cell C4 in the example.

4. Type the equal sign (=).

5. Type FLO and wait for the options to appear, then select FLOOR. Note that Sheets tells you the syntax of the function when you select it this way.

How to use the FLOOR function in Google Sheets Picture 2 How to use the FLOOR function in Google Sheets Picture 3

6. Click the cell with the rounded value. The cell name must appear in the formula inside the brackets.

How to use the FLOOR function in Google Sheets Picture 4

7. Enter a comma.

8. Click the cell with the coefficient.

How to use the FLOOR function in Google Sheets Picture 5

9. Press Enter and the results will appear.

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

Normally, a hint box will appear with the option to autofill the rest of the table. Click the checkmark to automatically populate the table.

How to use the FLOOR function in Google Sheets Picture 6

Example 2: Use numbers instead of cell references

You don't have to use cells to enter formulas, you can also use values ​​directly. For example, if the same table of values ​​is reused, in cell C4, the formula:

=FLOOR(A4,B4)

Can be replaced with:

=FLOOR(38.5,3)

How to use the FLOOR function in Google Sheets Picture 7

When you press Enter , you will get the results. However, it won't let you populate the table automatically. You also cannot use the fill handle as it will only apply the same numbers to the function instead of the others in the table. So if you enter numbers instead of using cell references, you will have to fill in each result individually.

Result interpretation

How to use the FLOOR function in Google Sheets Picture 8

If looking 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 less than 38.5, resulting in 36.

The coefficient can also be in decimal point, for example, in row 6, the factor is 0.1. So the function is now looking to round down to the nearest tenth instead of an integer. Therefore, the result will be decimal in tenths. In the above example, we have 12.2.

Sometimes you have a negative value. In the example below, the article changed the value in cell A8 from 6.7 to -6.7. The result of the FLOOR function will become -8. This is because -8 is the closest lower number to -6,7 which is also a multiple of 2.

How to use the FLOOR function in Google Sheets Picture 9

When to use the FLOOR function in Google Sheets?

You can use the FLOOR function to round numbers when dealing with things like currencies, such as rounding foreign exchange rates. Sometimes after currency conversion, there are too many decimal points.

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 with the format menu in Google Sheets by going to Format > Number > Custom currency .

You can also nest the FLOOR function with other functions to perform complex calculations.

This tutorial includes a comprehensive look at the basics of the FLOOR function in Google Sheets. However, that's only the tip of the iceberg considering what this powerful spreadsheet program can do. Keep practicing to master the FLOOR function and every aspect of Google Sheets.

Lesley Montoya

Update 07 February 2023