FLOOR
Rounds a number down to the nearest multiple of a significance value.
FLOOR(<number>, <significance>)Returns: A number (a scalar).
What it does
FLOOR rounds a number down to the nearest multiple of a value you choose. Where ROUNDDOWN works on decimal places, FLOOR works on steps: rounding to the nearest 0.25, the nearest 5 dollars or the nearest 1,000 units. Use it when a price, a block size or a billing increment must land on a set step below the original number.
Syntax and arguments
- number: the value to round down.
- significance: the step size, such as 0.25 or 1000. It is required.
Example
The pricing team sets retail prices that must end in a multiple of 5 cents. The calculated price is rounded down to the nearest 0.05 so the posted price is never above the approved ceiling.
Posted Price = FLOOR(Product[UnitPrice], 0.05)
A calculated price of 12.37 becomes 12.35. The list stays on the approved grid, and the price does not exceed the figure the team approved.
Example: a second, different use
Bill a service in whole blocks of 15 minutes, dropping any partial block:
Billable Hours = FLOOR([Logged Minutes], 15) / 60
A time entry of 47 minutes becomes 45 minutes, which is 0.75 hours. Use CEILING instead if partial blocks must be billed.
Things to watch
- The significance argument is required. A missing value is an error.
- FLOOR with a negative number and a positive significance behaves in a way that is easy to get wrong. Keep the numbers positive, or test the result carefully.
- Use the same significance as the source system. A different step gives a different answer.
- FLOOR is not the same as INT, which works on whole units. Use INT when the step is 1.
- Rounding a total with FLOOR can differ from the sum of rounded rows. Pick the level you want.
In business terms
FLOOR is used for price points, billing blocks, container sizes, budget steps and any rule where an amount must fit a grid. Finance teams use it in pricing reviews, professional services billing and procurement, where the grid is part of the contract.