Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# How to create a tiered calculation

Posted on 2011-02-25
Medium Priority
485 Views
Hello,
I 'm trying to create a function that will return of tiered amounts.  For example, I have the the following tier
Tier                                         %
0 to 2,500,000                         0
2,500,00.01 to 5,000,000        4%
5,000,000 to 10,000,000         6%
10,000,000.01 to 15,000,000  8%
>=15,000,000.01                      In addition to the 8% above, 20,000 for each million above 15,000,000

Basically, if the input value is 16,000,000.  I want take 0% of the first tier, add it to 4% of the second tier, add that result to 6% of the third tier and so on
0
Question by:chtullu135
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 93

Expert Comment

ID: 34982398
>>In addition to the 8% above, 20,000 for each million above 15,000,000

What would the "bonus" amounts be for the following values:

15,999,999.99
16,000,000.00
16,000,000.01
16,500,000.00
16,999,999.99

0

LVL 3

Accepted Solution

JAMcDo earned 2000 total points
ID: 34986189
Try this pair of functions.  It worked for me - assuming I interpreted your request properly.

Function TieredCalc(dblAmt As Double) As Double

Dim dblPercentAmt As Double

dblPercentAmt = 0

If dblAmt > 2500000 Then   'Calculate first tiered amount
dblPercentAmt = (MinOf2(dblAmt, 5000000) - 2500000) * 0.04
End If

If dblAmt > 5000000 Then  'Calculate 2nd tiered amount + 1st tiered amount
dblPercentAmt = dblPercentAmt + (MinOf2(dblAmt, 10000000) - 5000000) * 0.06
End If

If dblAmt > 10000000 Then                        'Calculate 3rd tiered amount + first 2 tiered amounts
dblPercentAmt = dblPercentAmt + (dblAmt - 10000000) * 0.08
End If

If dblAmt > 15000000 Then                       'Calculate 20000 per million over 15 million
dblPercentAmt = dblPercentAmt + Int((dblAmt - 15000000) / 1000000) * 20000
End If

TieredCalc = dblPercentAmt

End Function

Function MinOf2(dblFirst As Double, dblSecond As Double) As Double

If dblFirst < dblSecond Then
MinOf2 = dblFirst
Else
MinOf2 = dblSecond
End If

End Function

John
0

LVL 44

Expert Comment

ID: 34989703
I like that.  Well done John!
0

Author Comment

ID: 34997399
The bonus amounts would be
Amount
15,999,999.99
16,000,000.00
16,000,000.01
16,500,000.00
16,999,999.99
0

Author Comment

ID: 34997408
matthewspatrick:
The bonus amounts would be
Amount                      Bonus Amt
15,999,999.99                0
16,000,000.00             20,000
16,000,000.01             20,000
16,500,000.00             20,000
16,999,999.99             20,000
17,000,000                  40,000
0

Author Closing Comment

ID: 34997622
Thanks for the help.  I did come up with the following solution which also works but yours is much cleaner so I will use the one you came up with

Private Function GetESP(dblTotalPayment As Double) As Double
Dim dblIncentiveAmount As Double
Dim dblESP As Double

dblIncentiveAmount = dblTotalPayment
Dim dblTier1 As Double
Dim dblTier2 As Double
Dim dblTier3 As Double
Dim dblTier4 As Double
Dim dblTier5 As Double
Dim dblTotal As Double

dblTier1 = 0

If dblIncentiveAmount <= 2500000 Then
dblTier1 = 0
End If

If dblIncentiveAmount > 5000000 Then
dblTier2 = ((2500000) * 0.04)
End If

If dblIncentiveAmount > 10000000 Then
dblTier3 = ((10000000 - 5000000.01) * 0.06)
End If

If dblIncentiveAmount > 15000000 Then
dblTier4 = ((15000000 - 10000000.01) * 0.08)
End If

If dblIncentiveAmount > 16000000 Then

dblTier5 = ((dblIncentiveAmount - 15000000.01) * 0.08) + (Int(((dblIncentiveAmount - conMaximumIA) / 1000000)) * conOverAmt)
End If

dblTotal = dblTier1 + dblTier2 + dblTier3 + dblTier4 + dblTier5

GetESP = dblTotal

End Function
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
###### Suggested Courses
Course of the Month8 days, 20 hours left to enroll