NetSuite Rounding Formulas in Saved Searches

Use the Oracle SQL ROUND function when a NetSuite saved-search calculation needs a controlled number of decimal places:

ROUND({amount}, 2)

The second argument is the precision. A value of 2 rounds to two decimal places, 0 rounds to a whole number, and a negative value rounds digits to the left of the decimal point.

NetSuite lists numeric and date forms of ROUND in its supported SQL expressions. Do not assume that every function from generic Oracle documentation is available in saved-search formulas.

Quick rounding reference

Requirement	Formula
Two decimal places	`ROUND({amount}, 2)`
Whole number	`ROUND({quantity}, 0)`
Nearest ten	`ROUND({amount}, -1)`
Nearest hundred	`ROUND({amount}, -2)`
Always round down to an integer	`FLOOR({quantity})`
Always round up to an integer	`CEIL({quantity})`
Remove decimal places toward zero	`TRUNC({quantity}, 0)`
Round to the nearest day	`ROUND({datecreated})`
Remove the time portion	`TRUNC({datecreated})`

For ordinary numeric calculations, use Formula (Numeric) or Formula (Currency), depending on what the result represents.

Round a number to decimal places

The syntax is:

ROUND(number, decimal_places)

To round an extended quantity calculation to two decimal places:

ROUND(
    NVL({quantity}, 0) * NVL({rate}, 0),
    2
)

Use Formula (Currency) if the result is money. Use Formula (Numeric) for a general measurement or quantity.

If the precision argument is omitted, Oracle rounds to a whole number:

ROUND({quantity})

Writing 0 explicitly often makes the intent clearer:

ROUND({quantity}, 0)

Round to tens, hundreds or thousands

A negative precision rounds digits to the left of the decimal point:

ROUND({amount}, -1)

That rounds to the nearest ten.

ROUND({amount}, -2)

That rounds to the nearest hundred. Similarly, -3 rounds to the nearest thousand.

This is useful for dashboards where the exact amount creates noise, but keep the unrounded field available when users need to reconcile the result.

Round currency calculations

Round the complete calculation, not every input, unless the business rule explicitly requires line-level rounding.

Prefer:

ROUND(
    NVL({quantity}, 0) * NVL({rate}, 0),
    2
)

over:

ROUND(NVL({quantity}, 0), 2) *
ROUND(NVL({rate}, 0), 2)

Rounding intermediate values can produce a different total from calculating at full precision and rounding once at the end.

Currency display is not the same as calculation precision

Selecting Formula (Currency) controls how NetSuite treats and displays the result. It does not replace an explicit rounding rule inside the formula.

If a search is used for accounting reconciliation, confirm:

The currency represented by each row.
Whether values are transaction, base or consolidated currency.
Whether the required rule is line-level or total-level rounding.
Whether the native amount field has already been rounded by the transaction process.

ROUND changes numeric precision; it does not perform currency conversion.

Round percentages and ratios

Protect the denominator from both null and zero:

ROUND(
    NVL({grossprofit}, 0) /
    NULLIF({amount}, 0) * 100,
    2
)

This produces a numeric percentage such as 25.00.

Formula (Percent) can apply percentage formatting differently from Formula (Numeric). Test a known ratio before deciding whether the formula should return 0.25 or 25. Do not multiply by 100 merely because a percent sign is desired; first confirm what the selected formula type displays in your account.

For a Formula (Percent) result, the underlying ratio is commonly clearer:

ROUND(
    NVL({grossprofit}, 0) /
    NULLIF({amount}, 0),
    4
)

The additional decimal precision preserves enough information for percentage display.

`ROUND`, `TRUNC`, `FLOOR` and `CEIL`

These functions answer different questions.

`ROUND`: nearest value

ROUND({quantity}, 0)

Use this when values should move to whichever whole number is nearest.

`TRUNC`: remove decimal places toward zero

TRUNC({quantity}, 0)

TRUNC discards the decimal portion rather than rounding it. This differs from FLOOR for negative values.

`FLOOR`: next integer at or below the value

FLOOR({quantity})

For a negative number, FLOOR moves away from zero. For example, the floor of -1.2 is -2.

`CEIL`: next integer at or above the value

CEIL({quantity})

For 1.2, this returns 2. For -1.2, it returns -1.

Input	`ROUND(value, 0)`	`TRUNC(value, 0)`	`FLOOR(value)`	`CEIL(value)`
`1.2`	`1`	`1`	`1`	`2`
`1.8`	`2`	`1`	`1`	`2`
`-1.2`	`-1`	`-1`	`-2`	`-1`
`-1.8`	`-2`	`-1`	`-2`	`-1`

Negative test values matter. A formula that appears correct for sales quantities may behave differently when applied to credits or adjustments.

Always round up to a pack size

Suppose an item must be ordered in packs of 12. Divide by the pack size, round the number of packs up, then multiply back:

CEIL(
    NVL({quantity}, 0) / 12
) * 12

For a positive requirement of 13, this returns 24.

If the pack size is held in a custom field, protect against zero:

CEIL(
    NVL({quantity}, 0) /
    NULLIF({custitem_pack_size}, 0)
) * {custitem_pack_size}

Decide how a missing pack size should behave. Returning null may be safer than silently treating it as one.

Round durations

Subtracting Oracle dates produces a number of days. To show elapsed days to two decimal places:

ROUND(
    SYSDATE - {datecreated},
    2
)

To calculate whole calendar days, remove the time component before subtracting:

TRUNC(SYSDATE) - TRUNC({datecreated})

These are different calculations. The first measures elapsed time; the second compares calendar dates.

To express elapsed hours:

ROUND(
    (SYSDATE - {datecreated}) * 24,
    2
)

Use Formula (Numeric).

Round dates

ROUND also accepts a date. With no format argument, Oracle rounds to the nearest day, which means time values at midday or later can move to the following date:

ROUND({datecreated})

If the requirement is simply to remove the time, use TRUNC instead:

TRUNC({datecreated})

For reporting periods, a format model makes the boundary explicit:

TRUNC({trandate}, 'MM')

That returns the first day of the transaction’s month. Date rounding and numeric rounding share function names, but they follow different rules.

Round summary-search results

When a result column uses a summary type, consider where rounding belongs.

Round after aggregating

If the business rule is “sum the precise values, then round the total,” use a summed result and allow the final presentation to show the required decimals where possible.

An embedded aggregate formula may be appropriate in some searches:

ROUND(
    SUM(NVL({amount}, 0)),
    2
)

However, NetSuite does not allow aggregate and non-aggregate functions to be mixed incorrectly. Keep every displayed result properly grouped or summarised.

Round each row before aggregating

If the rule is “round each calculated line, then sum the rounded lines,” add this as Formula (Currency) and select Sum as its Summary Type:

ROUND(
    NVL({quantity}, 0) * NVL({rate}, 0),
    2
)

The two approaches can produce different totals. Choose based on the actual transaction or reporting rule, not visual preference.

Avoid converting rounded numbers to text

This formats a value as text:

TO_CHAR(
    ROUND({amount}, 2),
    'FM999999990.00'
)

It can be useful for a label or export, but it changes sorting and downstream behaviour. Text values sort lexically, so 100.00 may appear before 20.00.

Keep the result numeric whenever it needs numeric sorting, filtering, aggregation or further calculation.

Common rounding mistakes

Rounding only changes what I see

NetSuite field formatting may display fewer decimal places without changing the formula’s underlying calculation. Use ROUND when downstream calculations must operate on the rounded value.

Rounding every intermediate value

Early rounding compounds small differences. Unless a defined business rule says otherwise, calculate at full available precision and round the final result.

Treating `TRUNC` as `FLOOR`

They return different results for negative numbers. Test credits, refunds and adjustments rather than testing only positive sales.

Dividing by zero

Use NULLIF(denominator, 0):

ROUND(
    {amount} / NULLIF({quantity}, 0),
    2
)

Decide whether null is the correct business result or whether a CASE expression should supply a label or numeric fallback.

Using the wrong Formula type

Formula (Currency) for monetary output.
Formula (Numeric) for quantities, counts and general calculations.
Formula (Percent) for ratios intended for percentage display.
Formula (Date) for a rounded or truncated date.
Formula (Text) only after intentionally converting the result to text.

Test before relying on the result

Use a small test search containing:

Positive and negative values.
Values just below and above a rounding boundary.
Null and zero denominators.
Several currencies if the search spans currencies.
Both transaction lines and summary totals.

Add the unrounded source value beside the rounded formula while validating it. Remove the diagnostic column only after the edge cases agree with the business rule.