FLOOR.MATH function in Google Sheets

What Is FLOOR.MATH function in Google Sheets?

The FLOOR.MATH function in Google Sheets rounds a number down to the nearest integer or a multiple of a mentioned significance. If it’s a negative number, it would be rounded toward or away from zero, depending on the mode. FLOOR.MATH is handy for financial analysis when rounding off loans and interest rates.

The function takes one to three arguments: number is the numeric value to round down. The significance is the multiple to which you want to round down. The third argument, mode, controls the direction in which it rounds the negative values. For example, you have the number 16.7; to round it down to the nearest multiple of 3, we use the FLOOR.MATH function as follows.

=FLOOR.MATH(16.7, 3, 1)

It will return the value 15, which is the nearest multiple of 3 that is less than or equal to 16.7, using the rounding down mode.

FLOOR.MATH() Google Sheets Formula

Now that you have understood the basic use of FLOOR.MATH, let us look at its syntax.

=FLOOR.MATH(number, [significance], [mode])

1. number (mandatory) – The value to round down to the nearest integer
2. significance (optional)- The value to whose multiples the number will be rounded. It’s one by default.
3. mode (optional) – For negative numbers, the rounding direction is specified. If it is 0 or blank, it is rounded away from zero; else, it’s rounded towards zero.

How to Use FLOOR.MATH function in Google Sheets ?

We have seen above with a simple example of how the FLOOR.MATH function in Google Sheets works. To understand how to implement the function in Google Sheets, let us look at how we can use it with a simple example. Look at some numbers below that we require to round to the nearest multiple of significance.

Choose the cell where you wish to enter the formula for FLOOR.MATH.

Step 1: As seen below, we have three values. To find the nearest integer or the nearest multiple of significance of the first number in A2, we will apply the FLOOR.MATH formula in cell B2.

=FLOOR.MATH(

Step 2: Now, enter the parameters specified above, one at a time, as shown below.

=FLOOR.MATH(number, [significance], [mode])

Here, we enter the values =FLOOR.MATH(A2,4,1) as shown below. The four stands for the nearest multiple to which we must round the number.

Step 3: Press Enter to see the result 12 displayed in cell B2, which is the nearest multiple of 4 for the number 14.4.

Step 4: Proceed to use Autofill to get the results up to B4. You notice that it does not round off zero to the nearest multiple of 4.

Using the Google Menubar

1. You can easily enter the formula through the menu bar. Choose the cell where you want to enter the formula for FLOOR.MATH.
2. Proceed to the menu bar and click on “Insert” ➝ “Math” ➝ “Floor.Math.”
3. Enter the arguments as required. Close the bracket and press the “Enter” key.

Examples

The best feature of FLOOR.MATH function in Google Sheets, which sets it apart from FLOOR, allows us to control the rounding of negative numbers explicitly. Let us look at some interesting examples of how to leverage the FLOOR.MATH function in different ways.

Example #1 – Round down time values to the nearest hour or minute

Similar to the function CEILING.MATH in Google Sheets, we use FLOOR.MATH to round time off to the nearest hour/minute/second. Let us look at how to do so using the following example. We have the following times in column A:

Step 1: To round the time in cell A1 down to the nearest hour, you would enter the following formula in cell B1:

=FLOOR.MATH(A1, TIME(1, 0, 0))

Here, the TIME function has an output of 1:00:00, from which you can round off to the nearest time using the FLOOR.MATH function in Google Sheets.

Step 2: Now, drag the formula to cell B4 to get the time rounded down to the nearest hour for all the values.

Step 3: Now, for minutes, we use TIME(0, 1, 0). The formula to be entered in cell C1 is

=FLOOR.MATH(A1, TIME(0, 1, 0))

Step 4: Press Enter and use Autofill to get the other values rounded down to the nearest minute.

Example #2 – Determine the number of items that can fit into certain packaging sizes

A seller has to divide some items to be added to storage boxes for export. We have information on the number of different items available and the number of items that will fit into a storage box. We must find out how many storage boxes we need to fit the respective items. Let us look at how this can be done using FLOOR.MATH.

Step 1: As seen below, let us look at how the data is classified. Column A has the number of items, and Column B has the items that fit into each storage box.

Step 2: Look at the following formula in cell D2.

=FLOOR.MATH(A2,B2)

It rounds up the number of items (A2) to the nearest multiple of the number of items that can fit per storage box (B2).            

The result, divided by the number of items per box, shows how many storage boxes we need to accommodate different items. So, the formula in cell C2 will be

=(FLOOR.MATH(A2,B2)/B2) + 1

Now, since we are using the FLOOR.MATH in Google sheets, it rounds the value down to the nearest integer. To get the accurate value to accommodate all the containers, use the following formula in cell C2.

Step 3: Press Enter. You get the number of storage boxes you need to accommodate the chosen items.

Step 4: Use the formula for the other items by dragging it up to cell C6.

Example #3 – Round down monthly expenses or interest rates

We can also use the FLOOR.MATH function to round down our monthly expenses to understand them better. Note that the FLOOR.MATH function is handy when dealing with financial calculations where rounding down to a specific increment is essential. For example, if you have an individual’s monthly expenses and need to round them down to the nearest $10, you can use FLOOR.MATH in Google Sheets for this purpose.

The monthly expenses are in a spreadsheet below.

Step 1: To round down these expenses to the nearest $10, you can use the FLOOR.MATH function in Google Sheets is as follows:

Here, we use the formula =FLOOR.MATH(B2,10).

Step 2: Press Enter. You can see how it rounds the value to the nearest $10.

Step 3: Drag the formula to round the other expenses as well.

Important Things To Note

1. By default, the significance is set to 1. Also, the mode has no impact on positive numbers.
2. FLOOR.MATH ensures that the result when rounding negative numbers is also according to requirement, maintaining consistency in rounding down regardless of the number’s sign.
3. You get the #VALUE! error when the “number” or “significance” parameter is invalid or contains incorrect data.
4. The #NUM error occurs when the significance parameter is zero or negative, and the number is positive.
5. If both the number and significance are negative, the number is rounded up.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand FLOOR.MATH function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What is FLOOR.MATH function in Google Sheets. We learn definition, how to use FLOOR.MATH function in Google Sheets. You can learn more from the following articles –

Pivot Table Group By Month In Google Sheets

Calculate Simple Interest In Google Sheets

COUNTBLANK In Google Sheets