Have you ever needed to round a number down to the nearest specified factor? Look no further! The FLOOR function in Google Sheets is here to save the day. Whether you want to round prices or time values, FLOOR has got you covered.
Syntax
The syntax for the FLOOR function is as follows:
=FLOOR(value, factor)
- value is the number you want to round down.
- factor is the multiple you want to round down to.
Possible Errors
When using the FLOOR function, you may encounter the following errors:
- #NUM!: This error occurs when the value and factor have different signs. Make sure they are both positive or negative.
- #VALUE!: This error occurs when one of the arguments is not numeric.
Examples
Let’s dive into some examples to see how the FLOOR function works its magic!
Example 1: Rounding to the Nearest Nickel
Sometimes, it’s best to present a price rounded to the nearest nickel. Let’s see how to do that using the FLOOR function.
The formula used in cell C2: =FLOOR(A2, B2)
We use the FLOOR function to round the original price to a multiple of $0.05. As the function rounds down, it rounds $1.23 to $1.20 in row 2 and rounds $1.27 to $1.25 in row 4. Google Sheets doesn’t change the value in row 3 as it is already a multiple of $0.05.
Example 2: Rounding to the Nearest Half Hour
Next, let’s round some time values. It’s common to refer to a time as a rounded value. In this example, we’ll round the values to the nearest half-hour.
The formula used in cell C2: =FLOOR(A2, B2)
⚠️ You can enter a half-hour as either “0:30” or “0:30:00”. In both cases, you’ll need to use custom formatting to remove the seconds, as shown in the example above in column B.
Both rows 2 and 4 are rounded to the previous half-hour. As 1:30 PM in row 3 is already a multiple of 30 minutes, the FLOOR function leaves it unchanged.
Example 3: Rounding with Different Factors
So far, we’ve been modifying the value. Now, let’s focus on changing the factor. We’ll use both positive and negative values to see the differences.
You can see that the negatives and positives shift towards the same absolute values. Each value produced in column C is the opposite sign when the input values change sign.
Ready to try it out in your own Google Sheet? Make a copy of this spreadsheet to get the examples firsthand.
For more exciting articles on topics like Google Sheets and other online marketing tools, visit Crawlan.com.
