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:
=COMMISSION(20000)
or as shown here:
=COMMISSION(B3)
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.