ROUND

You test the VAT on the purchase journal (INK) of Veldhuis Installatie BV. The auditfile gives you the net amount and the VAT rate per entry. You recalculate the VAT yourself, to the cent, so you can compare it with the amount the client booked on account 1800, and you want to know which way an amount ending in exactly half a cent goes.

Formula in D2

=ROUND(B2 * C2, 2)

=ROUND(B2 * C2; 2)

Result: €227.33€ 227,33

Watch out: If the export stored the VAT rate as 21 instead of 21%, the formula returns 100 times too much VAT without any error. Check that C holds 0.21, shown as 21%.

You prepare an overview of the client's trial balance (saldibalans) at 31 December 2025 for the audit file, in whole thousands. Debit balances are positive and credit balances negative. You want one formula to copy down, and you want to switch between thousands and hundreds without editing it.

Formula in D2

=ROUND(C2, $F$2)

=ROUND(C2; $F$2)

Result: €128,000.00€ 128.000,00

Watch out: Rounded balances rarely add up to the rounded total. -48000 here, but ROUND of the raw total -47030 gives -47000. A reader who foots your overview finds a difference of 1000; say in a note that the figures are rounded individually.

=ROUND(C2 / 1000, 0)

=ROUND(C2 / 1000; 0)

You recalculate the 2025 straight-line depreciation in the client's fixed asset register (activastaat). Each asset shows cost, annual rate and the months in use.

The client books every line in whole cents, and the total in the register is the sum of those lines. If your recalculated total is built from unrounded lines, it can differ from the register by a cent and you chase a difference that does not exist. You need a total built from the rounded lines.

Formula in F2

=SUMPRODUCT(ROUND(B2:B6 * C2:C6 * D2:D6 / 12, 2))

=SUMPRODUCT(ROUND(B2:B6 * C2:C6 * D2:D6 / 12; 2))

Result: €12,367.91€ 12.367,91

Watch out: If the months column holds text from the export, such as 'n/a' for an asset that was sold, the multiplication returns #VALUE! for that element and the whole total becomes #VALUE!. Check with ISNUMBER or clean the column.

=ROUND(SUMPRODUCT(B2:B6, C2:C6, D2:D6) / 12, 2)

=ROUND(SUMPRODUCT(B2:B6; C2:C6; D2:D6) / 12; 2)

=SUM(ROUND(B2:B6 * C2:C6 * D2:D6 / 12, 2))

=SUM(ROUND(B2:B6 * C2:C6 * D2:D6 / 12; 2))

The holding charges a management fee invoice of 8,435.198.435,19 to five group companies by a fixed key. You recalculate the intercompany charges to reconcile the balances.

Each share must be in whole cents, and the five shares must add up to 8,435.198.435,19 to the cent, otherwise the intercompany balances do not agree with the invoice. Rounding each share on its own overshoots by a cent here; the last line has to absorb the difference, and the formula must make that visible instead of hiding it.

Formula in C2

=LET(
  total, E1,
  shares, B2:B6,
  rounded, ROUND(total * shares, 2),
  gap, ROUND(total - SUM(rounded), 2),
  last, SEQUENCE(COUNT(shares)) = COUNT(shares),
  rounded + IF(last, gap, 0)
)

=LET(
  total; E1;
  shares; B2:B6;
  rounded; ROUND(total * shares; 2);
  gap; ROUND(total - SUM(rounded); 2);
  last; SEQUENCE(COUNT(shares)) = COUNT(shares);
  rounded + IF(last; gap; 0)
)

Result: €2,530.56 / €2,108.80 / €1,687.04 / €1,265.28 / €843.51€ 2.530,56 / € 2.108,80 / € 1.687,04 / € 1.265,28 / € 843,51

Watch out: The mirror-image mistake is ROUNDDOWN or ROUNDUP per share to force the direction. Every share then leans the same way, the gap grows with the number of lines, and the last line carries a correction of several cents that looks like an error.

=ROUND($E$1 * B2, 2)

=ROUND($E$1 * B2; 2)