The FLOOR function in Google Sheets helps you quickly round down a number to its nearest lower value multiple of its factor.
However, please do not confuse the FLOOR function with the ROUND function since the ROUND function rounds down a number to its specific number of decimals.
Let’s check what the rules of Google Sheets’ FLOOR
function are.
FLOOR
function Rules:
- The function only requires two arguments to yield the returning value.
- The
FLOOR
function’s value can either be a positive number or a negative number. - The function’s factor must always be positive.
- Do not input a non-numeric argument. Otherwise, the return value is an error message #VALUE!.
- Numbers with the same signs are rounded down toward the nearest and most negligible value, with positive numbers towards zero and negative numbers away from the zero value.
- The
FLOOR
function follows the standard mathematical equation when computing numbers with the same and mixed signs.
Quick Example of FLOOR function Usage
Kye, an accounting specialist, needs to get the estimated total value of their monthly receivables. With the number of companies he handled, Kye, unfortunately, needed to extract and transfer the data to Google Sheets.
To make his life easier, he uses Google Sheets’ IMPORT RANGE function to extract all accounts receivable data from different companies.
After gathering the information, Kye immediately uses the FLOOR
function to see the estimated total receivables.
The given data are as follows:
Company | Accounts Receivable | Factor |
Zian’s Cafe | 10,000,485.89 | 1,000,000 |
Leonila’s Bistro | 5,483,292.93 | 1,000,000 |
Cessy’s Pet Store | 3,984,927.58 | 1,000,000 |
Wambol’s foodhauz | 1,203,930.78 | 1,000,000 |
After computing the data, Kye pulls up the returning value as follows:
Company | Resulting Value |
Zian’s Cafe | 10,000,000.00 |
Leonila’s Bistro | 5,000,000.00 |
Cessy’s Pet Store | 3,000,000.00 |
Wambol’s foodhauz | 1,000,000.00 |
With the help of the Google Sheet spreadsheets FLOOR
function, =FLOOR(10,000,485.89, 1,000,000), Kye was able to efficiently do his job in a very short period.
The Anatomy of the FLOOR Function
Every Google Sheet function has its structure. For the FLOOR
function, here’s how it is written.
Syntax:
=FLOOR(value, [factor])
Now, let’s understand the parts of the FLOOR
function.
- = the equal sign will start the formula. It notifies Google Sheets that the next character keyed in is a formula.
- FLOOR() is our
FLOOR
function. - Value refers to the number that we need to round down.
- Factor is a number whose multiples are the basis to round down the value.
A Real Example of Using FLOOR Function
Let us have a quick example to understand the function’s application further.
Suppose you are given a task to compute specific data, and they are needed to be run down precisely to the lowest and nearest multiple numbers of a given value.
Here is the given value:
To quickly and efficiently generate the answer, you will now use the FLOOR
function, and with what you learned on how to write the function, you then input the FLOOR
function in the ‘returning value cells.’ Within seconds, you were able to pull the results.
To further understand how it was done, let us go through the FLOOR
function’s syntax with the step-by-step guide below, and for you to follow the steps, you may click the link below for the sample spreadsheet.
Get on board, and let’s start writing!
How to Use FLOOR Function in Google Sheets
- Firstly, prepare your data in the spreadsheet. You must make sure that the data that you key in are all correct. Otherwise, it will yield a different value.
Also, take note of the numerical signs. If the given data are supposed to be all negative, then both the value and the factor must have a negative sign.
2. After you input the data, you now select the cells for your returning value. We designated the cells C2 to C10 for the FLOOR
formula results in our example.
3. Now that we have input everything, we will start writing the formula.
Whenever you write a function in Google Sheets, start it with an ‘=‘ sign, followed by the FLOOR
function, and then an open parenthesis ‘(. ‘
4. We will now add the value of the number from which we will round down to its nearest multiple integers, followed by a comma ‘,‘ symbol.
You can either use the value’s reference cell or the value itself in this step.
Formula 1:
Formula 2 :
5. After adding the value and the comma, we will add the factor to the function’s syntax. Once adding the factor, close the function with a close parenthesis ‘).’
6. Once done with the function, hit the ‘ENTER‘ key to get the returning value.
Frequently Asked Questions
Is Google Sheets’ FLOOR
function similar to Google Sheets’ ROUND?
No, the FLOOR
function is different from the ROUND
function. The former rounds down a number to its nearest multiple integers. While the latter rounds the number to a certain number of decimal places.
What is the FLOOR function for?
Google Sheets’ FLOOR
function has a broad scope. It can be used to round down big numbers, currencies, and time.
Why does the FLOOR function return an error message?
The FLOOR
function returns an error message when the value or factor entered is not of the same signs or they are non-numeric.
And that’s it for today’s tutorial. You can now start using the function without worries as you already understand the fundamentals of the FLOOR
function.
If you have some questions and want to clarify something, you can comment below or email us, and we will surely assist you with your queries.
You can also check some of our published Google Sheet functions, formulas, and articles like this on our website www.sheetaki.com. Also, don’t forget to gently click the “Newsletter” tab to not miss any of our Google Sheet uploads.