ROUND
Round a number to a given number of decimal places, or to tens, hundreds and thousands with a negative count. Halves round away from zero.
Syntax
ROUND(number, num_digits)| Argument | Required | Description |
|---|---|---|
number | Required | The number to round. |
num_digits | Required | Decimal places to keep. 0 rounds to a whole number; negative values round to the left of the decimal point. |
Examples
Every example below was executed against VisiGrid engine 0.54.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=ROUND(2.345,2) | 2.35 | |
=ROUND(1.005,2) | 1.01 | Rounds the decimal you typed, not the binary value just below it that the computer stores. |
=ROUND(A2*B2*C2,2) | 4.95 | Sales tax on three items, to the cent. |
=ROUND(1234.567,-2) | 1200 | A negative count rounds to hundreds. |
=ROUND(2.5,0) | 3 | |
=ROUND(-2.5,0) | -3 | Halves go away from zero, in both directions. |
Rounding versus formatting
A number format that shows two decimal places changes what you see; the cell still holds every digit, and every formula that reads it uses them. Add up a column of prices formatted to cents, each with a hidden fraction of a cent, and the total can disagree with the sum of what is displayed by a cent or two.
ROUND changes the value itself. Round each line where the real-world amount
is rounded — a tax charge, an invoice line — and the total adds up the way a
person checking it by hand expects.
Halves go away from zero
ROUND(2.5,0) is 3 and ROUND(-2.5,0) is -3. This is the rule Excel and most
people use. It is not banker’s rounding (halves to the nearest even number),
which some programming languages and databases use by default, so a total
computed in one of those can differ from the spreadsheet by a cent.
1.005 rounds to 1.01
Most decimals cannot be stored exactly in binary: 1.005 is really 1.00499999999999989… A naive rounding routine sees a number just under the half and rounds down to 1.00. VisiGrid, like Excel, first recovers the decimal to 15 significant figures, so the half you typed is the half that gets rounded.
Always up or always down
ROUNDUP rounds away from zero whatever the next digit
is, and ROUNDDOWN towards zero. They take the same
arguments.
Excel compatibility
Matches Excel, including rounding halves away from zero and correcting for binary representation (ROUND(1.005,2) is 1.01).