How to Use the FLOOR Function in Google Sheets

Google Sheets is one of the top spreadsheet programs, offering a range of easily accessible functions, especially when compared to Excel. One such function is the FLOOR function. Read on to understand how the FLOOR function works and when to use it in Google Sheets.

What is the FLOOR function in Google Sheets?

The FLOOR function rounds down a specified element value to the nearest multiple. This means that the FLOOR of a value will typically be lower than or equal to the value itself. The article will provide a more detailed explanation after reviewing the syntax.

Syntax of the FLOOR function

The FLOOR syntax uses the function name followed by the value to be rounded and the divisor specified in parentheses.

=FLOOR(value,factor)

The FLOOR function operates as follows:

The =FLOOR is the function name that specifies the type of calculation to be performed.
The value is the number to be rounded.
The factor is the number that the result must be a multiple of.

How the FLOOR function works:

The FLOOR function will provide you with 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 the factor is 1, then the FLOOR of this value is 1 because 1 is the largest value that is less than 1.3.

If the value is an integer, its FLOOR will be the same as the value itself. For example, if the value is 7 and the specified factor is 1, then its FLOOR will be 7. This occurs because 7 is also the nearest integer to itself.

It is important to note that FLOOR does not round down to non-multiple values of the factor. For example, if you specify 8 as the value and 3 as the factor, the result will be 6 because it is the nearest multiple of 3 when rounded down.

Examples of the FLOOR function in Google Sheets

Now that you understand what the FLOOR function is and how it works, let’s look at some examples. While you can use actual numbers within the FLOOR function, you will likely use cell references. You can follow these general steps to use the FLOOR function with cell references:

Quick Analysis

  1. Enter your values into cells.
  2. Enter your factors into adjacent cells.
  3. In an empty cell next to the factor, enter the formula =FLOOR(value, factor) where value is the cell reference of the value, and factor is the cell reference of the factor or the factor itself.
  4. Press Enter to get the result.
  5. Use the fill handle or the autofill feature in Google Sheets to populate 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 value table:

  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 on the cell where the first result will appear. This is cell C4 in the example.
  4. Type an equal sign (=).
  5. Type FLO and wait for the options to appear, then select FLOOR. Note that Sheets will show you the syntax of the function as you select it this way.
  1. Click on the cell with the value to be rounded. The cell name should appear within the parentheses in the formula.
  1. Enter a comma.
  2. Click on the cell with the factor.
  1. Press Enter and the result will appear.

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

Typically, a suggestion box will appear with the option to autofill the rest of the table. Click on the checkmark to autofill the table automatically.

Example 2: Using values instead of cell references

You don’t have to use cells to input the formula; you can also directly use values. For example, if you reuse the same value table, in cell C4, the formula would be:

=FLOOR(A4,B4)

This can be replaced with:

=FLOOR(38.5,3)

When you press Enter, you will receive the result. However, it will not allow you to autofill the table. You also cannot use the fill handle because it will only apply the same number to the function instead of different numbers in the table. So, if you input values instead of using cell references, you will have to manually fill in each individual result.

Explanation of the result:

Looking at row 4, the given value is 38.5. The factor is 3, so the FLOOR syntax calculates the nearest number to 38.5 that is a multiple of 3 and less than 38.5, resulting in 36.

The factor can also be in decimal form. For example, in row 6, the factor is 0.1. Therefore, the function is now trying to round down to the nearest tenth instead of a whole number. As a result, the value will have a decimal in the tenths place. In the example above, it is 12.2.

Sometimes you may 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 nearest number that is lower than -6.7 and is also a multiple of 2.

When to use the FLOOR function in Google Sheets?

You can use the FLOOR function to round numbers when dealing with things like currency, such as rounding exchange rates. Sometimes, after converting currencies, there may be too many decimal places.

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 desire. You can also accomplish this with the formatting menu in Google Sheets by going to Format > Number > Custom currency.

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

This guide provides a comprehensive overview of the basic concepts of the FLOOR function in Google Sheets. However, it barely scratches the surface when considering what this powerful spreadsheet program is capable of. Continue practicing to become proficient in using the FLOOR function and all aspects of Google Sheets.

Leave a Comment

Your email address will not be published. Required fields are marked *

Shopping Basket
Scroll to Top