FLOOR.MATH Function in Excel

In this post, you will learn about the Floor.math function in excel, how to use it in-with the excel-spreadsheets. The function rounds off a number towards the closest multiple of significance.

FLOOR.MATH Function in Excel

Syntax

=FLOOR.MATH(number, significance, mode)

The syntax has three arguments:

  • number – This argument holds the number to round off.
  • significance – This argument holds multiple to which you want to round the number, the default value is 1. (optional)
  • mode – For rounding negative numbers towards or away from zero, default value 0.
  • The Floor.math function rounds off the given number to the closest multiple of the significance value.
  • The arguments can be an integer or a decimal number.
  • Note that the number is rounded off according to the multiples and not the number itself Example =FLOOR.MATH(21, 9) answer is 18 a multiple of 9.

How to use Floor.math function in Excel?

  • The Floor.math function rounds off a number towards the closest multiple of significance. This is a rounding function similar to the Ceiling function but, it rounds down the number towards zero, that is towards the number lesser than it, hence the name floor.
  • The sign of the significance argument does not affect the function in any way, whether positive or negative.
  • When working with positive numbers the numbers are rounded down towards zero.

The example below shows the Floor.math function with the default significance value.

How to use Floor.math function in Excel?

Sometimes when the significance value is 1, the function returns the number unchanged as the answer. This happens when there is no need to round up the number as it satisfies the significance value.

The example below shows the function with the assigned significance values.

How to use Floor.math function in Excel?
  • When working with negative numbers we can control the direction of rounding off the number, towards zero or away from zero using the mode argument.
  • The mode argument has a default value 0, which rounds off the negative number away from zero, towards the next lesser multiple.
  • Note: -6 is lesser than -5, that is the number away from zero is smaller.
  • To change the direction, replace the default value 0 with any number or the character ‘true’.
  • And the mode argument only works for negative numbers and does not affect positive numbers.

The example below displays the function with default argument values

How to use Floor.math function in Excel?

This example shows the function with the default mode argument value, this performs the same function and rounds it up away from zero.

How to use Floor.math function in Excel?

The following example uses 1 and ‘true’ as the mode argument values, this rounds up the number towards zero.

How to use Floor.math function in Excel?