Excel VBA FLOOR Function: Round Down to a Multiple

VBA & macros · Updated November 21, 2026

Excel VBA does not have a built-in function named FLOOR that you can call directly, but you can use Excel’s worksheet function through Application.WorksheetFunction.Floor_Math. For example, it rounds 17.6 down to a multiple of 5, producing 15; if you need a reusable worksheet formula, you can also write a small VBA function of your own.

What “floor” means in Excel

Flooring a number means rounding it down to a selected multiple, also called the significance. If the multiple is 5, possible results include 0, 5, 10, 15, and 20. Flooring 17.6 to a multiple of 5 gives 15. Flooring 17.6 to a multiple of 0.5 gives 17.5. This is different from rounding to the nearest value: a number such as 17.6 rounds to 18 to the nearest whole number, but floors to 17 when the multiple is 1.

The word “down” needs care when the number is negative. In mathematical floor behavior, down means toward negative infinity, not toward zero. For example, flooring -7.2 to a multiple of 1 gives -8, because -8 is the greatest integer that is no greater than -7.2. This distinction matters for dates, temperatures, balances, and any data that can fall below zero.

In Excel, FLOOR is the name people often use for this operation, but Excel offers more than one related worksheet function. VBA code must call a worksheet function through Excel’s object model or implement the calculation itself.

Call FLOOR.MATH from VBA

For Microsoft 365 on Windows, the modern choice is the worksheet function FLOOR.MATH. In VBA, its name uses an underscore in place of the period: Floor_Math. The following macro rounds a value down to the nearest multiple of 5 and displays the result.

Sub ShowFloorMath()
    Dim originalValue As Double
    Dim stepSize As Double
    Dim roundedValue As Double

    ' Set the number and the multiple to round down to.
    originalValue = 17.6
    stepSize = 5

    roundedValue = Application.WorksheetFunction.Floor_Math(originalValue, stepSize)
    MsgBox CStr(roundedValue)
End Sub

With these assigned values, the message displays 15. The first argument is the number to round; the second is the significance, or multiple. You can replace the example numbers with variables, cell values, or values passed into another procedure. For example, code can read a number from a worksheet cell, pass it to Floor_Math, and write the result to another cell.

Before running a macro that changes workbook data, save a separate copy of the workbook. Macro actions generally cannot be reversed with Ctrl+Z, so a backup is useful even when the code appears simple. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need the code to remain in the file.

Use the worksheet function in a cell

If the result is needed in a worksheet rather than only inside a macro, enter the Excel formula directly. With a number in A2 and a desired multiple of 5, use:

=FLOOR.MATH(A2,5)

If A2 contains 17.6, the result is 15. This is often simpler than writing VBA: the result recalculates when the input changes, and other users can see the calculation in the formula bar. VBA is more useful when the flooring step is part of a larger automated task, when a button runs a process, or when you want a custom function or workflow.

For a decimal multiple, provide that multiple as the second argument. With a value in A2, this formula floors it to the nearest half-unit:

=FLOOR.MATH(A2,0.5)

If A2 is 17.6, the returned value is 17.5. The displayed number of decimal places depends on the cell’s number format; formatting changes what is shown, not the underlying result. Excel 365 includes FLOOR.MATH. If a workbook must work in older Excel versions, verify function availability in the target version before distributing it.

Understand negative numbers and the mode argument

FLOOR.MATH accepts an optional third argument called mode. The default behavior for negative values rounds away from zero, which corresponds to rounding toward negative infinity. For example, -7.2 with a significance of 1 becomes -8. Setting mode to a nonzero value changes the handling of negative numbers so they round toward zero: -7.2 becomes -7. Positive values are unaffected by this mode choice.

With the value in A2, this formula rounds a negative value toward zero at a multiple of 1:

=FLOOR.MATH(A2,1,1)

For example, if A2 contains -7.2, the result is -7. Use this option only when that direction is what the calculation requires. It is not the same as mathematical floor for negative values, and silently choosing the wrong direction can shift totals or bucket assignments.

A common source of confusion is comparing floor with VBA’s Fix function. Fix(-7.2) returns -7 because it truncates toward zero, while Int(-7.2) returns -8 because it rounds down to the next integer. Neither function alone rounds to arbitrary multiples, but Int can be used to build that calculation.

Build a reusable VBA function

If you want to use floor-to-a-multiple logic in several places, you can create a custom VBA function in a standard module. This version treats a zero significance as zero and uses the absolute size of the significance. Its behavior for negative numbers follows mathematical floor, like the default behavior of FLOOR.MATH.

Public Function FloorToMultiple(ByVal Number As Double, _
                                ByVal Significance As Double) As Double
    Dim stepSize As Double

    ' A zero multiple has no useful interval, so return zero.
    If Significance = 0 Then
        FloorToMultiple = 0
        Exit Function
    End If

    stepSize = Abs(Significance)
    FloorToMultiple = Int(Number / stepSize) * stepSize
End Function

In Excel, with the number to round in A2, call the function like this:

=FloorToMultiple(A2,5)

If A2 contains 17.6, the result is 15; if it contains -7.2, the result is -10 when the significance is 5. The calculation divides by the step size, applies Int to round the quotient down, then multiplies by the step size. For a significance of 1, the negative example returns -8 instead. Because this is a user-defined function, the workbook must retain its VBA code and macros must be permitted for the function to calculate.

To add the code in desktop Excel for Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the function into the module. On a Mac, Option+F11 opens the editor on many keyboards; some require Fn as well, and the shortcut can depend on macOS keyboard settings. Excel for the web can display a workbook containing VBA, but it does not run VBA macros or custom VBA functions. Use desktop Excel to create and run them.

Choose between FLOOR, FLOOR.MATH, and Int

Excel’s older FLOOR worksheet function exists for compatibility, but its treatment of negative values and sign combinations differs from the newer function. In particular, older FLOOR behavior can return an error when the number and significance have different signs. For current Excel work, FLOOR.MATH is generally clearer because its negative-number mode is explicit. When maintaining an older workbook, do not replace its FLOOR calls without checking whether the changed negative-number behavior affects the results.

Inside VBA, Application.WorksheetFunction.Floor_Math delegates the calculation to Excel’s worksheet function. That avoids reimplementing its options, but invalid arguments can raise a VBA runtime error. A custom function based on Int avoids a worksheet-function call and is convenient when you need a fixed, well-defined rule. It does not automatically reproduce every option or every edge case of Excel’s functions.

  • Use a worksheet formula when you want a visible, automatically recalculating result.
  • Use WorksheetFunction.Floor_Math when VBA needs Excel’s modern floor behavior during a macro.
  • Use a custom VBA function when you need a reusable rule in cells or code and can define its behavior precisely.
  • Use Int rather than Fix when constructing mathematical floor logic for negative values.

Common errors and precision issues

One frequent error is trying to call Floor as if it were a native VBA function. VBA does not recognize a bare call such as Floor(value, 5) unless a function with that name has been defined in the project. Call Excel’s worksheet function through Application.WorksheetFunction, or use a custom function such as the one above.

Another issue is passing a zero significance to a worksheet function. Excel function behavior and error handling can vary by function, so do not assume that zero is a useful interval. In the custom function shown here, zero is explicitly defined to return zero. If your application should instead reject zero, add validation and return an appropriate error or stop the procedure rather than allowing an unintended answer.

Floating-point numbers are stored with finite binary precision. Decimal fractions such as 0.1 cannot always be represented exactly, so a result may contain a tiny internal difference even when it displays as a clean decimal. This can affect later equality checks or calculations. Format the cell for readability, and where exact decimal arithmetic is essential, consider scaling values to integers—for example, store currency in cents—provided the scale and range fit the task.

Also check that the significance matches the intended unit. Flooring a price to 0.05, a time value to 15 minutes, and a measurement to 0.5 are different requirements, even though they use the same basic operation. Test representative positive, negative, exact-multiple, and zero inputs before applying a macro to a large range.

Frequently Asked Questions

Is there a native FLOOR function in VBA?
No. VBA has functions such as Int and Fix, but not a built-in function named Floor for rounding to a multiple. Call Excel’s FLOOR.MATH through Application.WorksheetFunction, or write a custom VBA function.

What does VBA return when flooring -7.2 to a multiple of 1?
With mathematical floor behavior, the result is -8, because -8 is below -7.2. A method that truncates toward zero returns -7 instead. Choose the direction deliberately, especially when negative values are possible.

Can I use my VBA floor function in a worksheet cell?
Yes. Put a public custom function in a standard VBA module, save the workbook in a macro-enabled format, and call it from a cell. It will not calculate in Excel for the web, which does not execute VBA.

Why does WorksheetFunction.Floor_Math cause a VBA error?
The call may fail if an argument is invalid, a cell value cannot be converted to a number, or the Excel version does not support the worksheet function. Check the inputs and the target version; use a custom implementation if you need a specific fallback rule.

Should I use Int or Fix for rounding down?
Use Int when negative values must round toward negative infinity: Int(-7.2) is -8. Fix truncates toward zero, so Fix(-7.2) is -7. For rounding to multiples, apply the chosen behavior to the value divided by the significance.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.