How to Use the FLOOR Function in Excel

The FLOOR function in both Excel and Google Sheets is designed to round a number down to the nearest multiple of a specified significance. The syntax for utilizing the FLOOR function is as follows:

FLOOR(number, significance)

Here’s a breakdown of the parameters:

• number: The numerical value that you wish to round down.
• significance: The multiple to which the number should be rounded down.

Basic Usage

To better understand how the FLOOR function operates, consider the following example. Assume you need to round the number 15.75 down to the nearest multiple of 5. The applicable formula would be:

=FLOOR(15.75, 5)

This formula returns 15, as it is the largest multiple of 5 that does not exceed 15.75.

Examples of Applications

The FLOOR function can be particularly useful in a variety of scenarios:

Rounding Prices

For instance, to round a list of prices down to the nearest dollar, you can apply the FLOOR function as follows. If the price is located in cell A1, you would enter the following formula in cell B1:

=FLOOR(A1, 1)

Rounding Time Values

Similarly, if you need to round time values down to the nearest hour, the FLOOR function can be used. Suppose the time is displayed in cell A1, then the formula in cell B1 would be:

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

This formula will efficiently round the time value down to the nearest hour.

Handling Negative Numbers

When rounding negative numbers, the FLOOR function rounds towards negative infinity. For example, to round -10.5 down to the nearest multiple of 3, you would use:

=FLOOR(-10.5, 3)

This will return -12, as this is the nearest multiple of 3 that is less than or equal to -10.5.

Utilizing the FLOOR function allows for seamless rounding down of numbers to specified multiples in both Excel and Google Sheets, meeting a variety of data manipulation needs.

More information: https://support.microsoft.com/en-us/office/floor-function-14bb497c-24f2-4e04-b327-b0b4de5a8886