To round a decimal down in Excel, use FLOOR.MATH with the increment you want. With a value of 3.87 in A2, =FLOOR.MATH(A2,1) returns 3; with the same value in A2, =FLOOR.MATH(A2,0.1) returns 3.8. The first formula floors to a whole number, while the second floors to a multiple of one tenth.
What FLOOR Means in Excel
In mathematics, the floor of a number is the greatest integer that is less than or equal to that number. For a positive value such as 3.87, that integer is 3. Excel’s FLOOR functions extend the idea: they can round down to a chosen multiple, not just to an integer. An increment, also called a significance, tells Excel which multiples are allowed.
That distinction matters. Flooring 3.87 to a whole number gives 3, but flooring it to the nearest lower multiple of 0.5 gives 3.5. Excel does not simply remove decimal places; it finds a multiple of the specified increment that is no greater than the original value.
For current Microsoft 365 on Windows, FLOOR.MATH is usually the clearest choice. It accepts a number, an optional significance, and an optional mode. If you leave significance out, Excel uses 1, so the result is a whole-number multiple. If you leave mode out, Excel uses its default behavior for negative values, described below.
Round Decimal Values Down to an Integer
To discard the fractional part by rounding downward to a whole number, set the significance to 1. With 12.99 in A2, =FLOOR.MATH(A2,1) returns 12. With 12 in A2, the same formula returns 12: flooring an integer does not reduce it by one. This is useful when the result must be a whole-number threshold or count, provided rounding down is appropriate for the task.
To apply the calculation to a column, put the original values in A2:A20 and enter the formula in B2, with A2 holding the first value: =FLOOR.MATH(A2,1). If A2 contains 6.4, B2 returns 6. Copy the formula down column B to calculate the other rows. Excel adjusts the cell reference for each row, so the next row uses A3, then A4, and so on.
FLOOR.MATH returns a numeric result, not text. You can use that result in later calculations, comparisons, or charts. Changing the number format to show zero decimal places is not the same thing: formatting only changes what appears on screen, while FLOOR.MATH changes the stored result returned by the formula.
Choose the Increment for Decimal Places
The significance controls the step size. Common choices include 1 for whole numbers, 0.1 for tenths, 0.01 for hundredths, and 0.05 for five-cent increments. The number of decimal places shown in a cell does not automatically determine the significance; you must choose the increment that matches the rule you want.
- Whole numbers: significance 1 keeps results on multiples of 1.
- Tenths: significance 0.1 keeps results on multiples such as 3.7, 3.8, or 3.9.
- Hundredths: significance 0.01 keeps results on multiples such as 3.86 or 3.87.
- Custom steps: significance 0.25 uses quarter-unit steps; significance 5 uses steps of five.
For a value of 3.87 in A2, =FLOOR.MATH(A2,0.05) returns 3.85, because 3.85 is the greatest multiple of 0.05 that does not exceed 3.87. For a value of 17.42 in A2, the same formula returns 17.40. These examples show why a significance is not simply a request to display a certain number of decimal places: the allowed results are multiples of that significance.
Use a positive, nonzero significance for ordinary work. A significance of zero is not a meaningful step size and can produce an error. If the step size is stored in a worksheet cell, check that the cell contains a numeric value and is not blank or text.
How FLOOR.MATH Handles Negative Numbers
Negative values are where the everyday phrase round down can be confusing. On a number line, moving down means moving toward negative infinity. Therefore, flooring negative 3.2 to a whole number gives negative 4, not negative 3: negative 4 is the greatest integer that is still less than or equal to negative 3.2.
With -3.2 in A2, =FLOOR.MATH(A2,1) returns -4. FLOOR.MATH also has an optional mode that changes its treatment of negative values. With -3.2 in A2, =FLOOR.MATH(A2,1,1) returns -3, rounding the result toward zero instead. The mode argument is usually unnecessary for positive values, but it matters when a calculation includes negative numbers.
This is one reason not to assume that every function described informally as rounding down behaves identically. The older FLOOR function is retained for compatibility, but its negative-number behavior differs and, in some cases, a negative number with a positive significance returns an error. For predictable handling of negative values in Microsoft 365, use FLOOR.MATH and choose the mode deliberately.
FLOOR Compared with INT, TRUNC, and ROUNDDOWN
FLOOR.MATH, INT, TRUNC, and ROUNDDOWN can all reduce a decimal, but they answer different questions. INT rounds a number down to the nearest integer. TRUNC removes the fractional part and moves toward zero. ROUNDDOWN rounds toward zero to a specified number of decimal places. FLOOR.MATH rounds down to a multiple you choose.
For a value of -3.2 in A2, =INT(A2) returns -4, while =TRUNC(A2) returns -3. The difference is important: truncating negative 3.2 moves toward zero, whereas flooring it moves toward negative infinity. For positive values, INT and TRUNC often appear to give the same result, which can hide the distinction until a negative value enters the data.
ROUNDDOWN is useful when the requirement is to retain a fixed number of decimal places while moving toward zero. With 3.879 in A2, =ROUNDDOWN(A2,2) returns 3.87. That is not the same as flooring to the nearest multiple of 0.05, which uses a different set of permitted results. Choose based on the rule: decimal places, whole-number floor, or a particular increment.
Enter and Fill the Formula in Excel
In Excel for Microsoft 365 on Windows, click the cell where you want the result, type the formula, and press Enter. To calculate a list, enter a formula that refers to the first source cell, then copy it down. You can drag the fill handle at the bottom-right corner of the selected cell, or select the formula cell and the cells below it and press Ctrl+D on Windows.
Excel for Mac uses the same FLOOR.MATH function and formula syntax. The fill-down shortcut is Command+D rather than Ctrl+D. In Excel for the web, the function is available in current versions, though keyboard shortcuts and interface details can vary by browser and platform. Formulas shown here use commas between arguments; some regional settings use semicolons as argument separators, so Excel may adjust what you type to match your settings.
When copying a formula, check that the cell reference changes as intended. A reference such as A2 normally becomes A3 in the next row. If you instead need every result to use the same increment stored in a fixed cell, use an absolute reference for that increment before copying. This prevents the significance reference from shifting down the column. Confirm a couple of copied results against hand-calculated examples before relying on a large set of results.
Common Errors and Decimal Precision
A result that looks surprising often comes from using an unsuitable significance, a text value, or the wrong function. For example, a significance of 0.1 means steps of one tenth; it does not mean “keep one decimal place” in every possible rounding situation. A value can also be displayed with fewer decimal places than it actually contains, so a visible 3.8 might internally be slightly below or above 3.8.
Excel stores most decimal fractions using binary floating-point representation, so some decimal values cannot be represented exactly in memory. This can matter when an imported value is extremely close to a step boundary. If a source value is intended to be 2.50 but contains a tiny extra difference from a system export, flooring to tenths could produce a lower result than expected.
If you know the data has insignificant extra digits, you can round the input to an appropriate precision before flooring it. With an imported value of 2.499999999 in A2, =FLOOR.MATH(ROUND(A2,2),0.1) returns 2.5. Use this approach only when two decimal places are genuinely the intended precision: rounding first changes the input and can affect legitimate values near the boundary. Do not use it as a blanket fix without checking the source data and the calculation rule.
If Excel shows an error, verify that the number and significance are numeric, the significance is not zero, and the formula uses the intended function. If a formula is displayed literally in the cell rather than calculating, the cell may be formatted as Text or the entry may have a leading apostrophe. Change the cell to a suitable number format, then re-enter the formula.
Practical Uses and Choosing the Right Rule
FLOOR.MATH is useful when values must align to a lower boundary. Examples include grouping measurements into fixed intervals, counting complete units, applying a price increment, or moving a timestamp to the start of its day. The essential question is not merely how many decimals to show; it is which lower multiple is valid for the purpose.
For example, a packing rule might count only complete groups of six items. Flooring a quantity to a significance of 6 returns a multiple of six, whereas flooring to 1 only removes the fractional part. A pricing rule might require a result on five-cent increments, while an engineering worksheet might use increments such as 0.25. These are calculation choices, not formatting choices.
For dates and times, Excel stores dates as serial numbers and times as fractions of a day. Flooring a date-time serial to a whole number can therefore move the time back to midnight of that date; the cell may need a date format to display the result as a calendar date. Before applying FLOOR.MATH to dates, confirm that the intended step is measured in Excel serial units and that the result format makes sense.
For business rules involving money, quantities, or measurement tolerances, confirm the required direction and increment before choosing a formula. Flooring always biases the result downward according to its rounding rule; it is not automatically the fairest or legally appropriate way to calculate a charge, allowance, or reported measurement. If the requirement is to round to the nearest increment rather than always down, use a rounding method designed for that rule instead.
Frequently Asked Questions
How do I round down to two decimal places in Excel?
Decide whether you mean truncate to two decimal places or floor to a hundredth increment. For ordinary positive values these often look the same, but their behavior can differ for negative numbers and for custom increments. ROUNDDOWN moves toward zero; FLOOR.MATH follows its floor behavior and accepts a significance.
What is the difference between FLOOR and FLOOR.MATH?
Both round to multiples, but FLOOR.MATH provides an explicit mode for handling negative values and is the modern choice in Microsoft 365. The older FLOOR function is maintained for compatibility and can behave differently with negative numbers, including returning an error in certain sign combinations.
Does FLOOR round to the nearest number?
No. FLOOR rounds down to a permitted multiple; it does not choose whichever multiple is closest. For instance, a value just above a boundary still goes to the lower boundary. Use a nearest-value rounding method if you want the closest multiple instead.
Why did FLOOR.MATH return a lower value than I expected?
Check the significance first: Excel may be flooring to a larger step than intended. Then inspect the full value in the formula bar, because displayed rounding can hide extra digits. Imported floating-point values near a boundary may also contain tiny differences that change which multiple is lower.
Can I use FLOOR.MATH for negative numbers?
Yes. Its default behavior rounds negative values toward negative infinity, so -3.2 goes to -4 when the significance is 1. If the intended rule is to move toward zero instead, use the mode argument and verify that this matches the calculation requirement.
Related
- Excel: Count How Many Times a Value Appears in a Range
- Excel Row Limit: How Many Rows Can a Sheet Hold?
- Excel Date Shortcut: Insert Today’s Date with Ctrl+;
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Column Letter to Number: Formula, Examples, and TipsTo convert an Excel column letter to its number, put the letter in A1 and enter =COLUMN(INDIRECT(UPPER(A1)&"1")) in…
- Excel SUM Based on Another Column: Formulas and ExamplesTo sum numbers in Excel according to a value in another column, use SUMIF: with categories in A2:A5…
- Excel VLOOKUP Column Index Number ExplainedIn Excel VLOOKUP, the column index number tells Excel which column to return a value from, counting from…
- Excel Date Plus 3 Months: Formula, Rules, and ExamplesTo add three calendar months to a date in Excel, use =EDATE(A2,3), assuming the starting date is in…
- How to Compare Two Columns in Excel With a FormulaTo compare two Excel columns row by row, enter =IF(A2=B2,"Match","Different") in C2, then fill the formula down. This…
- Excel Date Difference in Days: Formulas and ExamplesTo find the number of days between two dates in Excel, subtract the earlier date from the later…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.