If you are handling values both with and without value added tax (=VAT), you occasionally need to split values with VAT to values without VAT and VAT itself.

For example, 100 includes VAT 24 %. The value without VAT is 80,65 and VAT 19,35. We are working with two decimals.

Values are counted this way: 0,24/1,24*100 = 19,35 and 1/1,24 * 100  = 80,65.

You can calculate this way.

If you are repeating this process frequently, you might want to create your own formula. Also rounding to two decimals could be added into the formula.

Function z_vat(sum, per)

    z_vat = Application.WorksheetFunction.Round((per / (1 + per) * sum), 2)

End Function

Function z_vatzero(sum, per)

    z_vatzero = Application.WorksheetFunction.Round((1 / (1 + per) * sum), 2)

End Function

Write these formulas in Visual Basic editor. Functions are returning value for z_vat or z_vatzero with two input values sum and per. Sum is the sum with VAT and per is VAT percent.

Round function is used to limit the decimals to two. With Application.WorksheetFunction you can use normal Excel function in VBA.

Save the functions under personal workbook to have functions in use in all the Excel workbooks.

When writing a function, select fx insert function icon.

Select a category user defined function.

You can still double check. 80,65 * 0,24 =19,35.

As said, only input parameters are the sum and the percentage. The result of user defined function is already rounded.