General-Purpose Functions


Round value to nearest step


Round(value, step {1}, direction {0})


value - formula specifying a numeric value

step - formula specifying a rounding interval (optional, defaults to 1)

direction - formula specifying a rounding direction (optional, defaults to 0)


The direction parameter is interpreted as follows:

0 means round to the nearest step, e.g. if step is 1, round UP if the decimal part of value is >= 0.5 else round down

1 means always round up

-1 means always round down

For example, to round a calculated price to the nearest penny, use Round(price, 0.01).

Note that negative values are always rounded "away from zero", e.g.

round(-1.5, 1, 0) = -2

round(-1.5, 1, 1) = -2

round(-1.5, 1, -1) = -1

In other words negative values are rounded as if they were positive and then the minus sign is applied to the result.

(This is consistent with how =Round, =RoundUp, and =RoundDown work in Excel.)





Copyright © 2020-2024 Systematic Solutions, LLC