The FLOOR formula with example in Excel

The FLOOR function in Excel rounds a number down to the nearest multiple of a specified significance. The syntax for the FLOOR function is:

FLOOR(number, significance)

• number: The numeric value you want to round down.
• significance: The multiple to which you want to round down the number.

Example:

Let's say you have a set of numbers in column A, and you want to round them down to the nearest multiple of 5. Here’s how you can do it:

1. Enter your numbers in column A:

   • A1: 23
   • A2: 37
   • A3: 42
   • A4: 56

2. In column B, use the FLOOR function to round these numbers down to the nearest multiple of 5. In cell B1, enter the following formula:

   =FLOOR(A1, 5)

3. Drag the fill handle from B1 down to B4 to apply the formula to the other cells in column B.

Your worksheet will look like this:

A	B
23	20
37	35
42	40
56	55

Here’s a breakdown of what happens:

• 23 is rounded down to 20 (the nearest multiple of 5).
• 37 is rounded down to 35.
• 42 is rounded down to 40.
• 56 is rounded down to 55.

Additional Notes:

• If the number is positive, the function rounds the number down towards zero.
• If the number is negative, the function rounds the number away from zero.
• The significance must be a positive number.

Using the FLOOR.MATH function:

In newer versions of Excel (Excel 2013 and later), you can use the FLOOR.MATH function, which provides additional functionality such as controlling the rounding direction for negative numbers. The syntax is:

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

• number: The numeric value you want to round down.
• significance: (Optional) The multiple to which you want to round down the number (default is 1).
• mode: (Optional) Direction of rounding for negative numbers (default is 0).

Example using FLOOR.MATH:

Using the same numbers in column A, and rounding down to the nearest multiple of 5:

In cell B1, enter:

=FLOOR.MATH(A1, 5)

Then drag the fill handle from B1 down to B4.

This function works similarly but with additional control over the rounding process.

By using these functions, you can effectively round down numbers to a specified multiple in Excel.