FLOOR Function in Google Sheets: How to Use It with Examples

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

  1. value: The number you want to round down.
  2. 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

  1. The factor must be positive, but the value can be positive or negative.
  2. The factor cannot be zero.
  3. If you omit the factor, Google Sheets defaults it to 1.
  4. 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!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Count Consecutive Duplicates in Excel (Dynamic Array Formula)

Counting consecutive duplicates in Excel is useful for analyzing patterns, detecting repetitive sequences, and...

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Filter Top N per Category in Excel (Using FILTER & COUNTIFS)

You can use a combination of FILTER and COUNTIFS to filter the top N...

More like this

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Google Sheets: Extract Top N per Group from Query Aggregation

When working with grouped and aggregated data in Google Sheets, you might need to...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.