The single-argument function in this section is designed for sales managers who need to calculate the commissions earned by their salespeople. The commission rate depends on the monthly sales volume; those who sell more earn a higher commission rate. The function returns the commission amount based on the monthly sales (which is the function's only argument — a required argument). The calculations in this example are based on Table 20-1.

Table 20-1 |
Commission Rates by Sales |

Monthly Sales |
Commission Rate |

80-89,999 |
8.0% |

810,000-819,999 |
10.5% |

820,000-839,999 |
12.0% |

840,000+ |
14.0% |

You can use several approaches to calculate commissions for sales amounts entered into a worksheet. You could write a lengthy worksheet formula such as this:

=IF(AND(A1>=0,A1<=99 99.9 9),A1*0.0 8<IF(AND(A1>=100 00< A1<=19999.99),A1*0.105,IF(AND(A1>=20000,A1<=3 9999.99), A1*0.12,IF(A1>=40000,A1*0.14,0))))

A couple reasons make this a bad approach. First, the formula is overly complex. Second, the values are hard-coded into the formula, making the formula difficult to modify if the commission structure changes.

A better approach is to create a table of commission values and use a LOOKUP table function to compute the commissions:

=VLOOKUP(A1,Table,2)*A1

Another approach, which doesn't require a table of commissions, is to create a custom function:

Function Commission(Sales) | |

' Calculates sales commissions | |

Dim Tierl As Double, Tier2 As Double | |

Dim Tier3 As Double, Tier4 As Double | |

Tierl = 0.08 | |

Tier2 = 0.105 | |

Tier3 = 0.12 | |

Tier4 = 0.14 | |

Select Case Sales | |

Case 0 To 9999.99: Commission = Sales |
* Tierl |

Case 10000 To 19999.99: Commission = |
Sales * Tier2 |

Case 20000 To 39999.99: Commission = |
Sales * Tier3 |

Case Is >= 40000: Commission = Sales |
* Tier4 |

End Select | |

Commission = Round(Commission, 2) | |

End Function |

After you define this function in a VBA module, you can use it in a worksheet formula. Entering the following formula into a cell produces a result of 3,000. The amount of 25000 qualifies for a commission rate of 12 percent:

=Commission(2500 0)

Figure 20-1 shows a worksheet that uses the Commission function.

- |
I |
c |

■■Nrimp |
Sales_I |
Commission |

Was this article helpful?

## Post a comment