logo-mini

FLOOR Function

The floor function in Excel is a mathematical function that rounds a number down to the nearest integer. This function is useful for rounding numbers to specific intervals, such as rounding a number down to the nearest multiple of 10 or 100. It can also be used to round numbers to specific precision, such as rounding down to the nearest hundredth or thousandth.

The syntax for the floor function in Excel is:

=FLOOR(number, significance)

The number argument is the value that you want to round down. The significance argument is the interval or precision to which you want to round the number. For example, if you want to round a number down to the nearest multiple of 10, you would use 10 as the significance argument. If you want to round a number down to the nearest hundredth, you would use 0.01 as the significance argument.

There are a few key things to keep in mind when using the floor function in Excel:

  1. The significance argument must be a positive number. If you use a negative number as the significance argument, the function will return an error.
  2. If the significance argument is a power of 10 (e.g. 10, 100, 1000), the floor function will round the number down to the nearest multiple of that power of 10. For example, if you use 10 as the significance argument, the function will round the number down to the nearest multiple of 10.
  3. If the significance argument is a decimal (e.g. 0.01, 0.1, 0.001), the floor function will round the number down to the nearest multiple of that decimal. For example, if you use 0.01 as the significance argument, the function will round the number down to the nearest hundredth.
  4. The floor function uses the following rules to determine which integer to round the number down to:
  • If the number is positive and the significance argument is positive, the function will round the number down to the nearest multiple of the significance argument that is less than or equal to the number.
  • If the number is positive and the significance argument is negative, the function will round the number down to the nearest multiple of the significance argument that is greater than or equal to the number.
  • If the number is negative and the significance argument is positive, the function will round the number down to the nearest multiple of the significance argument that is greater than or equal to the number.
  • If the number is negative and the significance argument is negative, the function will round the number down to the nearest multiple of the significance argument that is less than or equal to the number.

Here are some examples of how the floor function works in Excel:

Example 1: Rounding to the nearest multiple of 10

Suppose you have a list of numbers in column A and you want to round them down to the nearest multiple of 10. To do this, you can use the following formula in column B:

=FLOOR(A1, 10)

Here’s what the results would look like:

A B
45 40
58 50
72 70
89 80
100 100

As you can see, the function rounded each number down to the nearest multiple of 10.

Download the sample files: FLOOR FUNCTION – Click here