» Create a Custom Function

Custom Function Example
Create a function named SalesTax to calculate the amount of Sales Tax (or VAT tax) included in the total amount line in an invoice.
The function has two argument boxes.

The function:
Type the code below into a regular Module sheet (read more on VBE & a Module sheet in Tip #19).

Function SalesTax (Total_Invoice, Tax_percentage) As Single
SalesTax = Total_Invoice – Total_invoice / (1+Tax_percentage)
End Function

Note: The SalesTax macro can be downloaded from www.exceltip.com/f1toc.

The formula structure is:
Function name = SalesTax
The Function arguments are placed in parentheses; the above example has two argument boxes.

The last piece of the formula, As Single, defines the value type of the result returned by the Function (a number with two decimal places in this case).
The Function receives the results of the calculation and returns the result to the cell.

Activating the function:
1. Type the total invoice into cell B2 and the tax percentage into cell B3.
2. Select cell D2, and then press Shift+F3 to open the Paste Function dialog box.
3. Select the User Defined category, and then select the SalesTax Function.
4. In the first argument box, select cell B2, and in the second argument box, select cell B3.
5. Click OK.
Screenshot // Create a Custom Function