Financial
Invoice calculation formulas: line discounts, SUMIFS subtotal, and tax-exempt flag
Build two-decimal invoice templates with per-line ROUND(Qty * Price * (1 - Disc%), 2), multi-invoice SUMIFS rollups, and Tax-Exempt customer overrides.
Round every extended line item to 2 decimals BEFORE summing the invoice
This example rounds each discounted line to two decimals before adding the lines, so the subtotal agrees with those rounded amounts.
It then computes tax once on the invoice subtotal. Other accounting or tax policies may require a different method.
| InvID (A) | Qty (B) | Price (C) | Disc (D) | LineNet = ROUND(B*C*(1-D),2) (E) |
|---|---|---|---|---|
| INV-501 | 4 | 125 | 10% (0.10) | 450.00 |
| INV-501 | 2 | 80 | 0% (0.00) | 160.00 (INV-501 Sub = 610.00) |
| INV-502 | 5 | 60 | 20% (0.20) | 240.00 |
Invoice Subtotal, Sales Tax, and Grand Total formulas
With 8.25% sales tax (0.0825) and TaxExempt = FALSE, INV-501 adds ROUND(610 * 0.0825, 2) = $50.33 tax for a $660.33 Grand Total.
=LET(sub, SUMIFS(E2:E4, A2:A4, "INV-501"), tax, IF(G2, 0, ROUND(sub * F2, 2)), sub + tax)Cent-rounded extended line item amount
Rounds Qty * UnitPrice * (1 - DiscountRate) to two decimal places.
=ROUND(B2 * C2 * (1 - D2), 2)Inputs and interpretation
This example adopts line-level net rounding followed by one invoice-level tax calculation at one illustrative rate. Taxability, exemption evidence, taxable base, currency precision and rounding rules depend on the applicable regime. Use valid booleans, numeric quantities/prices, and discount fractions in [0,1]. Binary floating point is not exact decimal accounting.
Try the example
Use the original values or change them to test your understanding.