You have the flexibility to enhance the capabilities of MS Excel spreadsheets by creating your own functions to supplement the built-in functions. While the built-in functions have certain limitations, these user-defined functions, also known as Visual Basic for Applications (VBA) functions, provide a valuable solution. By customizing the spreadsheet environment to suit individual requirements, the creation of user-defined functions becomes imperative.
Sales Volume($) | Commissons |
---|---|
<500 | 3% |
<1000 | 6% |
<2000 | 9% |
<5000 | 12% |
>5000 | 15% |
In table 15.1, when a salesman attains a sale volume of $6000, he will be paid $6000x15%=$720.00. A visual basic function to calculate the commissions can be written as follows:
Function Comm(Sales_V As Variant) as Variant If Sales_V <500 Then Comm=Sales_V*0.03 Elseif Sales_V>=500 and Sales_V<1000 Then Comm=Sales_V*0.06 Elseif Sales_V>=1000 and Sales_V<2000 Then Comm=Sales_V*0.09 Elseif Sales_V>=200 and Sales_V<5000 Then Comm=Sales_V*0.12 Elseif Sales_V>=5000 Then Comm=Sales_V*0.15 End If End Function
To create
VBA functions in MS Excel, you can click on tools,
select macro and then click on Visual Basic Editor as shown in Figure
15.1
Upon clicking the Visual Basic Editor, the VB Editor windows will appear as shown in figure 15.2. To create a function, type in the function as illustrated in section 15.1 above After typing, save the file and then return to the Excel windows.
In the Excel window, type in the titles Sales Volume and Commissions in any two cells. By referring to figure 15.3, key-in the Comm function at cell C4 and by referencing the value in cell B4, using the format Comm(B4). Any value appear in cell B4 will pass the value to the Comm function in cell C4. For the rest of the rows, just copy the formula by dragging the bottom right corner of cell C4 to the required cells, a nice and neat table that shows the commissions will automatically appear (as shown in figure 15.3). It can also be updated anytime.
Copyright©2008 Dr.Liew Voon Kiong. All rights reserved |Contact|Privacy Policy