Function QuarterlyBonus(PriorSales As Double, CurrentSales As Double, Quota As Double, SalesBrackets As Range, BonusPercent As Range) As Double
Dim i As Long, j As Long, n As Long
Dim Amount, Bonus As Double, Base As Double, Rate As Double
n = SalesBrackets.Cells.Count
If CurrentSales > Quota Then
Base = PriorSales + Quota
i = Application.Match(Base, SalesBrackets, 1)
For j = i To n
If j < n Then
Amount = Application.Min(SalesBrackets.Cells(j + 1).Value, PriorSales + CurrentSales) - Base
Base = SalesBrackets.Cells(j + 1).Value
Else
Amount = CurrentSales - Base
End If
If Amount > 0 Then Bonus = Bonus + Amount * BonusPercent.Cells(j).Value
Next
End If
QuarterlyBonus = Bonus
End Function
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
Make a Cell act like a Date | 7 | 39 | |
excel 2010 - Formatting Text in a cell to capitalize without using UPPER function | 12 | 41 | |
Dynamic control of Items in an Excel multiListBox | 7 | 29 | |
VBA: Insert new column and remove first character of string | 9 | 25 |
Join the community of 500,000 technology professionals and ask your questions.