Round down to multiples in Excel with FLOOR.MATH

You have 27 items and want complete groups of 5. You can allocate 25 items to complete groups, with 2 left over. Rounding down to a multiple answers that question. I first establish the direction and the meaning of the result, then choose the function.

The former Polish version of this page used ZAOKR.W.DÓŁ.DO.WIELOKR, a function name that does not exist in Excel. This guide uses the documented FLOOR.MATH, called ZAOKR.W.DÓŁ.MATEMATYCZNE in Polish. It is available in Microsoft 365 and versions including Excel 2016, 2019, 2021 and 2024.

Syntax: number, step and optional mode

According to Microsoft’s documentation, the function rounds down to an integer or a specified multiple:

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

Square brackets mark optional arguments; do not type them into the worksheet. significance is the step, such as 5 or 0.5. When omitted, a positive value is rounded down to a whole unit. mode controls the direction for negative numbers.

English examples here use a decimal point and commas between arguments. The Polish version uses localised function names, decimal commas and semicolon argument separators. Your own separators depend on the environment’s settings.

Complete groups versus number of items

The opening synthetic example becomes:

=FLOOR.MATH(27,5)

The result is 25: the number of items belonging to complete groups. To obtain the number of groups, divide by 5:

=FLOOR.MATH(27,5)/5

That gives 5 groups. The distinction matters. A result of 25 beneath a heading saying “number of packages” would describe the calculation incorrectly. I put the unit into the column heading.

If the question asks how many packages are needed to ship every item, rounding down leaves items unpacked. With 27 items and a capacity of 5, you need six packages, with the last one partly filled. That is a different operational question.

Fractional multiples

For a synthetic value of 14.7 and a step of 0.5:

=FLOOR.MATH(14.7,0.5)

The expected result is 14.5. The neighbouring multiples are 14.5 and 15; the lower one does not exceed the input. For a positive value of 49.99 and a step of 1, the result is 49.

The step needs the same unit as the original value. If a length is stored in metres, a step of 5 means five metres. Grouping in five-centimetre increments requires a step of 0.05 metres or a prior unit conversion. Correct syntax alone cannot detect that mismatch.

This guide uses positive steps. In a parameter column, I would reject non-positive steps as outside the exercise’s rule instead of guessing a universal error code for every rounding function. FLOOR, FLOOR.MATH and related functions have separate documented behaviour.

Define the direction for negative numbers

By default, FLOOR.MATH rounds negative values towards the more negative multiple. For -8.1 and a step of 2, the result is -10. Microsoft’s documentation includes that input and result:

=FLOOR.MATH(-8.1,2)

A non-zero mode can round negative values towards zero. This documented example uses -1:

=FLOOR.MATH(-5.5,2,-1)

It returns -4. In a sheet containing corrections or balances, establish whether “down” means a smaller number on the number line or a smaller absolute value. I record that choice with the calculation because either result can look reasonable without context.

Round a time down to its quarter-hour boundary

Suppose A1 contains an actual time value created by =TIME(10,37,0). To assign it to the beginning of its current quarter-hour interval:

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

Displayed with an hour-and-minute format, the result is 10:30. Excel time is a fraction of a day, so I build the step with TIME rather than use the number 15, which would represent fifteen days.

This example groups times; it does not define payroll rounding rules. If A1 contains the text 10:37, first convert it appropriately. If it contains a date and time, decide whether the date should remain part of the value. Changing the display format does not convert text into a numeric time.

Similar names describe different rules

MROUND chooses the nearest multiple. It does not always round up. For 27 with a step of 5, the nearest multiple is 25; for 28, it is 30.

ROUNDDOWN uses a number of digits rather than a group size. A second argument of 2 requests two decimal places. See the FLOOR guide for the older function and its sign rules.

Exercise: predict the result first

For synthetic quantities of 24, 25 and 26 with a step of 5, write down both the item count in complete groups and the group count. Expected item counts are 20, 25 and 25; expected group counts are 4, 5 and 5.

Then ask how many packages would ship all the items. Notice when rounding down stops matching the requirement. That is the most useful check here: recognising whether the chosen function answers the actual question.