Hi Patrick,
When the list of exceptions and discounted rates gets longer, and with more categories, the formulas get more complicated and error prone. With four categories (18%, 7%, 5% and 0%) you're at the point where I would switch from IF to a lookup table, or would include the rate as the last part of the product name, as you've done in your latest example.
A question arising from your new example:
All three amounts listed are 100.00
Reading from the bottom up…
The third, with a VAT rate of 0% is obviously correct - the VAT is 0 and the net amount paid is the same as the total amount paid.
The second, at 5% shows the VAT amount as €5.00, which is 5% of the total amount (100.00).
But the first, at 18% shows the VAT amount as €15.26, which is 18% of the Net paid amount (84.74), not the Total amount, for which 18% would be €18.00.
I suspect that one shows the correct calculations: The VAT, like the GST here, is added to the base price, and the result is the Total paid. The examples below work back from the Total to determine the net and VAT at the percentage rate applied to that net amount.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Here is the first example again, with calculations based on the assumption that the VAT is calculated by adding the specified percentage to the base (net) price.
The second example, placed directly below the first, uses the same assumption, and the same formulas, differing only in their references to column B (top) or column C (bottom) to collect the VAT rate applicable to that transaction, and in the column used as a trigger for the switch (described earlier).
Sales::J2: IF(LEN(H2)<1,"",H2÷("1."&RIGHT(B2,2)))
Expenses::J2: IF(LEN(C2)<1,"",H2÷("1."&RIGHT(C2,2)))
Sales::I2: IF(LEN(J2)<1,"",H2−J2)
Expenses::I2: IF(LEN(J2)<1,"",H2−J2)
All formulas are filled down from Row 2 to the last row of the table.
A note regarding the first example: Calculations on the table in the first example assumed the VAT rate was applied to the TOTAL price, and was deducted from the TOTAL amount to produce the Net amount.
The calculation in rows 2 and 4 of that table is correct for those assumptions. The calculations in rows 3 and 5 use the 18% VAT rate as I neglected to correct the spelling of Accommodation in the formulas in those rows.
Regards,
Barry