Excel ROUND Function

The Excel ROUND function returns a rounded number in accordance with the specified number of digits

Example: Excel ROUND Function

Excel ROUND Function

METHOD 1. Excel ROUND Function using hardcoded values

EXCEL

=ROUND(B5,2)
Result in cell D5 (5378.73) - returns a number rounded to the right of the decimal point by two.

=ROUND(B6,0)
Result in cell D6 (5379) - returns a number rounded to the nearest integer.

=ROUND(B7,-2)
Result in cell D7 (5400) - returns a number rounded to the left of the decimal point by two.

METHOD 2. Excel ROUND Function using links

EXCEL

=ROUND(B5,C5)
Result in cell D5 (5378.73) - returns a number rounded to the right of the decimal point by two.

=ROUND(B6,C6)
Result in cell D6 (5379) - returns a number rounded to the nearest integer.

=ROUND(B7,C7)
Result in cell D7 (5400) - returns a number rounded to the left of the decimal point by two.

METHOD 3. Excel ROUND function using the Excel built-in function library with hardcoded values

EXCEL

Formulas tab > Function Library group > Math & Trig > ROUND > populate the input boxes

=ROUND(B5,2)
Note: in this example we are rounding a number in cell (B5) to the right of the decimal point by two. 		Built-in Excel ROUND Function using hardocded values

METHOD 4. Excel ROUND function using the Excel built-in function library with links

EXCEL

Formulas tab > Function Library group > Math & Trig > ROUND > populate the input boxes

=ROUND(B5,C5)
Note: in this example we are rounding a number in cell (B5) to the right of the decimal point by the amount that is represented by the value cell (C5). 		Built-in Excel ROUND Function using links

METHOD 1. Excel ROUND function using VBA ROUND function with hardcoded values

VBA

Sub Excel_ROUND_Function()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("ROUND")

'apply the Excel ROUND function
ws.Range("D5") = Round(ws.Range("B5"), 2)
ws.Range("D6") = Round(ws.Range("B6"), 0)

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named ROUND.
Value to Round: Ensure that the value that you want to round is captured in range ("B5:B6").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("D5") and ("D6") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
Value to Round: Select the value that you want to round by changing the range ("B5:B6") to any range in the worksheet, that doesn't conflict with the formula.

ADJUSTABLE NOTES
Negative Rounding Limitation: The VBA ROUND function does not permit the use of a negative number of digits. An alternative method is to use the Application.WorksheetFunction.Round(), which is illustrated in Method 3 and 4.

METHOD 2. Excel ROUND function using VBA ROUND function with links

VBA

Sub Excel_ROUND_Function()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("ROUND")

'apply the Excel ROUND function
ws.Range("D5") = Round(ws.Range("B5"), ws.Range("C5"))
ws.Range("D6") = Round(ws.Range("B6"), ws.Range("C6"))

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named ROUND.
Value to Round: Ensure that the value that you want to format is captured in range ("B5:B6").
Number of Digits: Ensure that the number of digits to which the specified number should be rounded to is captured in range ("C5:C6").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("D5") and ("D6") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
Value to Round: Select the value that you want to round by changing the range ("B5:B6") to any range in the worksheet, that doesn't conflict with the formula.
Number of Digits: Select the number of digits to which the specified number should be rounded to by changing the range ("C5:C6") to any format that is within Excel's capability.

ADJUSTABLE NOTES
Negative Rounding Limitation: The VBA ROUND function does not permit the use of a negative number of digits. An alternative method is to use the Application.WorksheetFunction.Round(), which is illustrated in Method 3 and 4.

METHOD 3. Excel ROUND function using VBA with hardcoded values

VBA

Sub Excel_ROUND_Function()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("ROUND")

'apply the Excel ROUND function
ws.Range("D5") = Application.WorksheetFunction.Round(ws.Range("B5"), 2)
ws.Range("D6") = Application.WorksheetFunction.Round(ws.Range("B6"), 0)
ws.Range("D7") = Application.WorksheetFunction.Round(ws.Range("B7"), -2)

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named ROUND.
Value to Round: Ensure that the value that you want to round is captured in range ("B5:B7").

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("D5"), ("D6") and ("D7") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
Value to Round: Select the value that you want to round by changing the range ("B5:B7") to any range in the worksheet, that doesn't conflict with the formula.

METHOD 4. Excel ROUND function using VBA with hardcoded values

VBA

Sub Excel_ROUND_Function()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("ROUND")

'apply the Excel ROUND function
ws.Range("D5") = Application.WorksheetFunction.Round(ws.Range("B5"), ws.Range("C5"))
ws.Range("D6") = Application.WorksheetFunction.Round(ws.Range("B6"), ws.Range("C6"))
ws.Range("D7") = Application.WorksheetFunction.Round(ws.Range("B7"), ws.Range("C7"))

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named ROUND.
Value to Round: Ensure that the value that you want to round is captured in range ("B5:B7").

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("D5"), ("D6") and ("D7") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
Value to Round: Select the value that you want to round by changing the range ("B5:B7") to any range in the worksheet, that doesn't conflict with the formula.
Number of Digits: Select the number of digits to which the specified number should be rounded to by changing the range ("C5:C7") to any format that is within Excel's capability.

Usage of the Excel ROUND function and formula syntax

EXPLANATION

DESCRIPTION
The Excel ROUND function returns a rounded number in accordance with the specified number of digits.
SYNTAX
=ROUND(number, num_digits)
ARGUMENTS
number: (Required) The number that is to be rounded.
num_digits: (Required) The number of digits that the specified number will be rounded to.

ADDITIONAL NOTES
Note 1: The ROUND function can round to the right or left of the decimal point.
Note 2: To round to the right of the decimal point, enter a positive num_digits number.
Note 3: To round to the left of the decimal point, enter a negative num_digits number.
Note 4: Using a 0 as the number_digits number, the ROUND function will round to the nearest integer.