The FLOOR function in Google Sheets is used to round a number down to the nearest integer multiple of a specified significance. Although it’s similar to MROUND, which rounds to the nearest integer multiple, FLOOR always rounds down. This tutorial will explain how to use the FLOOR function effectively in Google Sheets, with examples.
Syntax
FLOOR(value, [factor])Formula Arguments
- value: The number you want to round down.
- factor (optional): The multiple to which the number should be rounded down. If omitted, it defaults to 1.
Understanding the Factor Argument
The factor in the FLOOR function is the multiple to which you want to round down the specified value. For example, if the factor is 5, the FLOOR function rounds the number down to the nearest multiple of 5.
Consider the number 14:
- If you use 5 as the factor, the FLOOR function rounds 14 down to the nearest multiple of 5, which is 10 (since 10 is the largest multiple of 5 less than 14).
Note that factors of 14 include numbers like 2 and 7 because they divide 14 exactly, but these are unrelated to the factor argument in the FLOOR function, which simply specifies the nearest multiple to which the number should be rounded down.
Example: Using the FLOOR Function in Google Sheets
Assume you have the value 8 in cell A2 and the factor 3 in cell B2. To use the FLOOR function with these cell references, enter this formula in cell C2:
=FLOOR(A2, B2)Alternatively, you can use specific values directly in the formula. For example:
=FLOOR(8, 3)Result: 6
Explanation: This formula rounds 8 down to the nearest multiple of 3.
- Multiples of 3 are 3, 6, 9, etc.
- Since 8 is between 6 and 9, rounding down to the nearest multiple of 3 gives 6.
If you wanted to round 8 up to the nearest multiple of 3, you’d use the CEILING function, which would return 9 in this case.
Important Notes
- The factor must be positive, but the value can be positive or negative.
- The factor cannot be zero.
- If you omit the factor, Google Sheets defaults it to 1.
- The factor can include decimal values, giving more flexibility in rounding.
Practical Applications of the FLOOR Function
The FLOOR function provides precise control over rounding, which can be useful in various scenarios:
Example 1: Rounding with Decimal Factors
=FLOOR(22.97, 0.15)Result: 22.95
In this example, the function rounds 22.97 down to the nearest multiple of 0.15, resulting in 22.95. This feature is beneficial when rounding currency or other values to specific increments.
Example 2: Rounding Time Values
You can use the FLOOR function to round time values down to specific increments, such as hours or minutes. For example:
=FLOOR(A2, "0:15")This formula would round down the time in cell A2 to the nearest 15-minute increment.
Note: The result will be a time value, so apply the time format by navigating to Format > Number > Time to ensure it displays correctly.
Key Takeaways
- The FLOOR function rounds a number down to the nearest multiple of a specified factor.
- It is handy for currency rounding, time rounding, or controlling decimal rounding in Sheets.
With this knowledge, you’re ready to start using the FLOOR function in Google Sheets. Enjoy!
