MROUND

In the planning phase you calculate overall materiality for five audit clients as 0.5% of revenue. The audit plan states materiality in whole thousands, so each calculated amount is rounded to the nearest 1,0001.000. You want the rounded amount next to each calculation.

Formula in C2

=MROUND(B2, 1000)

=MROUND(B2; 1000)

Result: €128,000.00€ 128.000,00

Watch out: A negative calculation, for example from a loss-making client when you use a result-based benchmark, such as -128,450128.450, gives #NUM! with a positive multiple. Use -1000 for negative amounts.

=ROUND(B2, -3)

=ROUND(B2; -3)

For a monetary unit sample you divide the book value of each specification by the planned sample size to get the sampling interval. The audit team works with intervals in whole thousands, so the interval is rounded to the nearest multiple of the step in E2. Keeping the step in a cell lets you change it to 5,0005.000 for a coarser interval without rewriting the formula.

Formula in D2

=MROUND(B2 / C2, $E$2)

=MROUND(B2 / C2; $E$2)

Result: €37,000.00€ 37.000,00

Watch out: Without the dollar signs, $E$2 becomes E3, E4 and so on when you copy down, and the rows below get an empty step. A step of 0 returns 0 for every interval.

=MROUND(B2 / C2, 1000)

=MROUND(B2 / C2; 1000)

You reconciled six ledger balances to the client's specifications. The difference is ledger minus specification: positive when the ledger is higher, negative when it is lower. For the summary in your memo each difference is rounded to the nearest 500 and must keep its sign. MROUND alone returns #NUM! on the negative rows, so the formula chooses the sign of the multiple to match the difference.

Formula in C2

=MROUND(B2, IF(B2 < 0, -$E$1, $E$1))

=MROUND(B2; IF(B2 < 0; -$E$1; $E$1))

Result: -€1,500.00€ -1.500,00

Watch out: MROUND(ABS(B2), $E$1) also avoids #NUM!, but it drops the sign: the receivables difference of -1,7301.730 becomes +1,5001.500. No error, and the summary shows the ledger higher than the specification when it is lower.

=ROUND(B2 / $E$1, 0) * $E$1

=ROUND(B2 / $E$1; 0) * $E$1

A retail client takes cash and rounds each receipt, VAT included, to the nearest 5 cents. You recalculate the expected cash amount from the net amount and the VAT rate (21% or 9%) and compare it with the amount booked.

Rounding the raw product directly is fragile, because net × (1 + rate) carries binary noise. The Master build first rounds the gross amount to whole cents, rounds those cents with MROUND, converts back to euros and returns the difference with the booked amount.

Formula in E2

=LET(
  net, B2,
  rate, C2,
  cents, ROUND(net * (1 + rate) * 100, 0),
  expected, MROUND(cents, 5) / 100,
  ROUND(expected - D2, 2)
)

=LET(
  net; B2;
  rate; C2;
  cents; ROUND(net * (1 + rate) * 100; 0);
  expected; MROUND(cents; 5) / 100;
  ROUND(expected - D2; 2)
)

Result: €0.05€ 0,05

Watch out: A difference of 0.05 is not automatically an error. Some systems round the VAT first and the total afterwards, which can differ by a cent from rounding the total. Confirm the client's rounding rule before you propose an adjustment.

=ROUND(MROUND(ROUND(B2 * (1 + C2) * 100, 0), 5) / 100 - D2, 2)

=ROUND(MROUND(ROUND(B2 * (1 + C2) * 100; 0); 5) / 100 - D2; 2)