What Is FLOOR Function In Google Sheets?
The FLOOR function Google Sheets is an inbuilt Math function. The function accepts two numbers. It then rounds down the first number to the closest integer multiple of the second number, cited as the significance factor.
Users can make use of the FLOOR function Google Sheets when they must round the foreign currency exchange rates. So, the function helps deal with the too many decimals issue post the currency conversion.
For instance, the source dataset shows a set of values and the corresponding significance factors to whose closest integer multiple we want to round down the cited values.
We shall consider the cell range C2:C6 as the target cells.
Then, we can implement the FLOOR(), which works like the Excel FLOOR function, in the target cells to secure the output, in line with the definition of the FLOOR function Google Sheets explained earlier.
The FLOOR() accepts two input values. The first argument value is a number, and the second is a significance factor, which is again a number.
Then, the FLOOR function Google Sheets returns a number obtained by rounding down the first number to the closest integer multiple of the factor.
For instance, in the case of the cell C2 formula, the FLOOR() accepts two inputs, 50 and 3. So, the FLOOR function Google Sheets returns 48 as the output. The reason is that 48 is the multiple of the factor 3, which is nearest to the cited number 50.
Please note that 51 is also a multiple of 3 and closer to the number 50. However, the function rounds down a number. So, the function considers 48 as the required value. On the other hand, if the given number is an integer multiple of the specified factor, the function returns the input number as the output.
Table of Contents
Key Takeaways
- The FLOOR function Google Sheetsis a function that rounds down a given number to the closest integer multiple of the cited significance factor.
- The FLOOR function in Google Sheets follows the rules stated below:
- When the value and factor argument values are positive, the function rounds down the value toward zero.
- When the value argument value is positive, and the factor argument value is negative, the function output is the #NUM! error.
- When the value argument value is negative and the factor argument value is positive, the function rounds down the value away from zero.
- When the value and factor argument values are negative, the function rounds up the value toward zero.
- When the value is an exact multiple of the factor argument value, the function does not round the value and returns the value as the output.
- The FLOOR function in Google Sheets helps to make numerical values more presentable in Financial and Statistical datasets.
FLOOR() Google Sheets Formula
The FLOOR function Google Sheets syntax is the following:
The following is the FLOOR function Google Sheets arguments explanation:
- value: The number that we want to round down to the closest integer multiple of the cited significance factor.
- factor: The number to whose multiple the given value will be rounded down. Also, while the argument’s default value is 1,the argument value cannot be 0.
Please ensure to supply the first argument value when applying FLOOR function Google Sheets, as it is mandatory. However, the second is optional, and when we ignore it, the FLOOR() considers its default value of 1.
How To Use FLOOR Google Sheets Function?
The following are the best ways of applying FLOOR function Google Sheets.
- Access the function from the ribbon.
- Enter the function into the sheet manually.
Method #1 – Access The Function From The Ribbon 🡢
Select a cell for displaying the result 🡢 The Insert tab 🡢 The Function option right arrow 🡢 The Math function group right arrow 🡢 The FLOOR function Google Sheets.
The selected function appears in the chosen cell, with the cursor inside the function brackets. We can now enter the FLOOR function Google Sheets arguments, separated by commas,inside the brackets.
Further, selecting the ‘?’ symbol against the function name shows the function syntax.
We can then select the down arrow in the syntax pane to know the definition of the FLOOR function Google Sheets explained with a basic example.
Finally, press Enter to fetch the function return value.
Method #2 – Enter The Function Into The Sheet Manually 🡢
- Choose the cell where we want to present the output.
- Type =FLOOR( in the cell. [ Alternatively, type =F or =FL and click the function name FLOOR from the listed suggestions to insert the function in the chosen cell.]
- Supply the argument values, separated by commas, and close the brackets.
- Press Enter to secure the FLOOR function return value.
Examples
The examples below describe the effective ways of using the FLOOR function in Google Sheets.
Example #1 – Using Cell References With The FLOOR Function
The source dataset lists trial results in column A.
We must round down them to the closest integer multiple of the cited corresponding significance factors. We shall consider cells C2:C5 as the target range.
Step 1: Select the first target cell, C2, and enter the FLOOR().
The FLOOR() gets inserted into the chosen cell. Next, enter the cell references, which are similar to Excel cell references, to the two required input values, separated by a comma.
[ Alternatively, select cell C2 and then Insert 🡢 Function à Math 🡢 FLOOR.
The FLOOR() appears in the chosen cell.
Next, update the cell references to the two argument values, separated by a comma, as depicted above.]
Step 2: Press Enter to view the FLOOR() output.
Next, utilizing the fill handle, which is similar to Excel fill handle, feed in the FLOOR() into the remaining target cells.
Let us check the cell C5 formula for the function logic.
The FLOOR() in cell C5 accepts the references to cells A5 and B5 as the inputs. They contain the number 9, which is the value argument value, and the number 5, which is the factor argument value.
So, the function rounds down the number 9 to the closest integer multiple of the significance factor of 5, which is 5.
Example #2 – Using Numbers Instead Of Cell References
The source dataset shows Stock A’s prices in column A.
We must round down them to the nearest integer multiple of the cited corresponding significance factors. Assume the target cells are C2:C6.
Step 1: Select the first target cell C2, enter the FLOOR(), and press Enter.
=FLOOR(125,10)
Step 2: Select the next target cell, C3, enter the FLOOR(), and press Enter.
=FLOOR(148.57,10)
Step 3: Select the next target cell, C4, enter the FLOOR(), and press Enter.
=FLOOR(129.89,10)
Step 4: Select cell C5, enter the FLOOR(), and press Enter.
=FLOOR(150,10)
Step 5: Select cell C6, enter the FLOOR(), and press Enter.
=FLOOR(105.65,10)
Let us check the cell C6 formula for the function logic.
The FLOOR() in cell C6 accepts the cells A6 and B6 values as the inputs. They are the number 105.65, which is the value argument value, and the number 10, which is the factor argument value.
So, the function rounds down the number 105.65 to the closest integer multiple of the significance factor of 10, which is 100.
Please note that if we directly supply the value of $105.65, with the ‘$’ symbol, to the FLOOR(), the function output will be #ERROR! error value. So, the directly supplied value argument valueshould be a number.
Furthermore, the target cells have the same currency format as the column A cells, leading to the function output appearing as currency values. Otherwise, the function output would appear as numbers without the currency format.
Example #3 – Round Down To The Next Nickel
The source dataset shows a set of rates of inventories in column A.
We must round down each rate value to the nearest integer multiple of the cited corresponding significance factor, which is the next Nickel. Consider column C cells are the target cells.
Step 1: Choose the first target cell C2, enter the FLOOR(), and press Enter.
=FLOOR(A2,B2)
Next, utilize the option of the fill handle to apply the FLOOR() in the other target cells.
Let us check the cell C5 formula to understand the function logic.
The FLOOR() in cell C5 accepts the references to cells A5 and B5 as the inputs. They contain the number 188.83, which is the value argument value, and the number 0.05, which is the factor argument value. Please note that we must round down the cited value to the next Nickel. So, the factor argument value is 0.05.
So, the function rounds down the number 188.83 to the closest integer multiple of the significance factor of 0.05, which is 188.80.
In this case, the factor is a decimal value. So, the function rounds down the decimal part of the value to the closest integer multiple of the factor.
Example #4 – Round Down To The Nearest Half-Hour
We have a set of employees and their swipe-in times.
We must round down the swipe-in times to the nearest half-hour and display the output in column D.
Step 1: Choose cell D2, enter the FLOOR(), and press Enter.
=FLOOR(B2,C2)
Next, utilize the fill handle option to update the function in the ensuing target cells.
Let us check the cell D6 formula to understand the function logic.
The FLOOR() in cell D6 accepts the references to cells B6 and C6 as the inputs. They contain the number 8:45, which is the value argument value, and the number 0:30, which is the factor argument value. Please note that we must round down the cited value to the nearest half-hour. So, the factor argument value is 0:30.
So, the function rounds down the number 8:45 to the closest integer multiple of the significance factor of 0:30, which is 8:30.
In this case, the value and factor argument values are decimal values at the backend. While the hour is the integer part, the minutes is the decimal part. So, the function rounds down the decimal part of the value to the closest integer multiple of the factor at the backend. Thus, we see the minutes part of the time getting rounded down.
Example #5 – Round With Different Factors
The source dataset shows the monthly balances in column A.
We must round down them to the closest integer multiple of the cited corresponding significance factors. We shall consider cells C2:C7 as the target range.
Step 1: Choose cell C2, enter the FLOOR(), and press Enter.
=FLOOR(A2,B2)
Next, utilize the fill handle option to implement the function in the rest of the target cells.
Let us check the cell C7 formula for the function logic.
The FLOOR() in cell C7 accepts the references to cells A7 and B7 as the inputs. They contain the number -505, which is the value argument value, and the number -150, which is the factor argument value.
So, the function rounds the number -505 to the closest integer multiple of the significance factor of -150, which is -450. Please note that both the argument values are negative. So, the value gets rounded up. On the flip side, if the value is negative and the factor is positive, the function rounds down the value.
Furthermore, if the value is positive and the factor is negative, the function output is the #NUM! error value.
Important Things To Note
- Assume an argument value is nonnumerical. Then, the FLOOR function Google Sheetsoutput is the #VALUE! error value.
- When the factor argument value is 0, the FLOOR function in Google Sheets returns the #DIV/0! error value.
- Assume we supply the value argument value directly to the FLOOR(), but the argument value is not a valid number. Then, the FLOOR function in Google Sheets returns the #ERROR! error value.
Frequently Asked Questions (FAQs)
The difference between FLOOR function and CEILING function in Google Sheets is as follows:
The FLOOR() rounds down the given number to the closest integer multiple of the cited significance factor.
On the other hand, the CEILING function rounds the given number up to the closest integer multiple of the cited significance factor.
For example, the source dataset contains a set of values and significance factors.
The steps to round down and up the values to the closest integer multiples of the significance factors are as follows:
Step 1: Choose cell C2, enter the FLOOR(), and press Enter.
=FLOOR(A2,B2)
Next, with the help of the fill handle, enter the FLOOR() in the remaining target cells.
Step 1: Choose cell D2, enter the CEILING(), and press Enter.
=CEILING(A2,B2)
Next, with the help of the fill handle, enter the CEILING() in the remaining target cells.
Let us check the row 6 formulas to check the logic.
The two functions, FLOOR and CEILING, accept the references to the cells A6 and B6 as the inputs. They contain the number 195, which is the value argument value, and the number 40, which is the factor argument value.
So, the FLOOR() rounds down the number 195 to the closest integer multiple of the significance factor of 40, which is 160. On the other hand, the CEILING function rounds up the number 195 to the closest integer multiple of the significance factor of 40, which is 200.
The difference between ROUNDDOWN and FLOOR in Google Sheets is as follows:
The ROUNDDOWN function rounds the given number to a specific count of decimal places, with the rounding being down to the ensuing valid increment.
On the other hand, the FLOOR() rounds down the given number to the closest integer multiple of the cited significance factor.
The FLOOR function is not working in Google Sheets due to the following reasons:
• An argument value is nonnumerical, leading to the FLOOR function returning the #VALUE! error value.
• The value argument value is positive, and the factor argument value is negative, making the FLOOR function return the #NUM! error value.
Download Template
This article must be helpful to understand the FLOOR Function Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is FLOOR Function Google Sheets. We learn how to use it to round down a number to the closest integer multiple with examples. You can learn more from the following articles. –
Leave a Reply