With a little bit of coding experience it is possible to create your own functions in Excel 2010 and earlier.

In Excel 2010, make sure the Developer toolbar is visible – if it is not, follow this post to make it so.

Now choose Developer tab and click the Visual Basic button. In the Project – VBAProject pane, select the current file and choose Insert > Module.



Type this function into the dialog and then close the VB editor and return to your worksheet:

Function Commission(Sales) As Currency
Commission = Sales * 0.05
If Commission > 1000 Then
Commission = 1000
End If
End Function


To test your function type this formula into a worksheet:

or as shown here:


The function calculates commission at 5% of the amount of sales. If the 5% value is more than $1,000 then the commission is pegged at this amount – to check this, test the function with a very large number. Functions created this way are only able to be used in the current workbook.

Helen Bradley