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, which we'll 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 down a value to its nearest multiple of a specified factor. This means that the FLOOR number of a value will usually be less than the exact value or number. We’ll explain in more detail after we’ve looked at the syntax.

FLOOR Function Syntax

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

        =FLOOR(value,factor)
    
  • =FLOOR: is the function name that specifies the type of calculation to be made.
  • value: is the number that is going to be rounded off.
  • factor: is the number that the result must be a multiple of.

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 the value 1.3 and a factor of 1, then the FLOOR number of this is 1 because one is the largest value to the nearest 1 that is less than 1.3.

If the value is a whole integer, its FLOOR will equal it. For example, if the value is 7, and we specify a factor of 1, then its FLOOR will be 7. This is the case because 7 is still the closest whole number.

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

Examples of the FLOOR Function in Google Sheets

So 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’ll most likely use cell references. You can use the following steps as a general guide for using the floor function with cell references:

A Quick Breakdown

  1. Input your values into the cells.
  2. Input your factors into the adjacent cells.
  3. On the empty cell next to the factor, put in the formula =FLOOR(value,factor) where value is the cell location of the value, and factor is either the cell location of the factor or the factor itself.
  4. Press Enter to get the results.
  5. Use the fill handle or autofill suggestion in Google Sheets to fill out the remaining cells in the column.

Example 1: Using Cell References With the FLOOR Function

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

An example table of values for the FLOOR function in Google Sheets
  1. Open a new Google Sheets document.
  2. Put in the values and factors you want to FLOOR. Make sure to label them clearly.
  3. Click on where the first result should appear. This is C4 in our example.
  4. Type the equals sign (=).
  5. Type FLO and wait for the options to appear, and select FLOOR. Note that Sheets tells you the function's syntax when you select it in this way.
  6. Click the cell with the value to be rounded off. The cell name should appear in the formula inside the bracket.
    Using a cell reference for the value in a Floor Function
  7. Type a comma.
  8. Click the cell with the factor.
    Using a cell reference as the Factor in a Floor Function
  9. Press Enter, and the result will appear.

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

Usually, a suggestion box will appear with the option to fill the rest of the table automatically. Click on the tick to autofill the table.

Autofill a table with Floor Functions

Example 2: Using Numbers Instead of Cell References

You don’t have to use the cell to input the formulas, you can also use the values directly, for example: If we were to use the same table of values again, in cell C4, the formula:

         =FLOOR(A4,B4)
    

Can be replaced with:

         =FLOOR(38.5,3)
    
Using numbers instead of cell references for a Floor Function

When you press Enter, it will give you the result. However, it will not allow you to fill the table automatically. You also can’t use the fill handle, as it will just apply the same numbers to the function rather than the others from the table. So, if you type the numbers instead of using cell references, you will have to fill in each result individually.

Explaining the Results

Floor Function results table

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

The factor can also be in decimal points, 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 a whole number. Therefore, the results will have a decimal value in the tenths. In the above example, we have 12.2.

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

In the below example, we changed the value in cell A8 from 6.7 to -6.7. The FLOOR function’s result will become -8. This is because -8 is the closest lower number to -6.7 that is also a multiple of 2.

Using the FLOOR function with negative numbers

When to Use the FLOOR Function in Google Sheets

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

For example, you can use the FLOOR function when converting US dollars into Euros to round off down to the nearest 1, 0.1, 0.01, or any other decimal place you’d like. You can also do this with the formatting menu in Google Sheets by heading 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 covered an in-depth look at the basics of the FLOOR function in Google Sheets. Yet, it’s 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.