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.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

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.