Tiered Pricing - Summary of a subset of tiered data

[SomeGuy84]

Hi folks,
It's a tiered pricing question! I've seen a great many posts about tiered pricing on here, although I don't seem to be able to find one that particularly addresses the situation I have. This may just be because it's a slightly cumbersome scenario to reduce to succint search terms, so I rather hope someone can either translate my issue into a link to another post, or assist me with it here.
So, tiered pricing. I have a set of data which consists simply of a number of transactions and some fees. I've created a little Sub to calculate the total value of the fees and the unit cost, and that all works nicely.
The issue arises from the fact that this number of transactions then needs to be broken down into two groups. Let me try to demonstrate what I mean (example data):
Total transactions = 50000
Total transactions in group A = 40000
Total transactions in group B = 10000
First 15000 transactions @ Tier 1 rate = 0.5p
Next 15000 transactions @ Tier 2 rate = 0.3p
Next 15000 transactions @ Tier3 rate = 0.2p
All transactions above 45000 @ Tier4 rate 0.15p
Transactions in group A are always priced first, followed by those in group B. This doesn't matter when the figures for the whole total of transactions are processed. However, when I need to calculate the value of the fees and unit cost just for group B, it quickly gets a bit tricky, because the tier that those group B transactions start at is dependant on how many transactions were in group A.
Even describing this I'm not sure it makes sense, so here's the actual data (I've changed some names because I think this may be potentially sensitive and might get me in a spot of bother at work )
First of all, here's one of the subs that calculates the overall tiered pricing, which I suspect I will need to alter to also do the pricing for 'Group B', which in reality is 'EU Transactions':

Sub CalcType1Fees(totalTransactions As Long)
    Dim tier1, tier2, tier3, tier4 As Long
    Dim rTier1, rTier2, rTier3, rTier4 As Double
    Dim totalEUTransactions As Long
    
    'get tier values and rates
    tier1 = Range("A30").Value
    rTier1 = Range("B30").Value
    
    tier2 = Range("A31").Value
    rTier2 = Range("B31").Value
    
    tier3 = Range("A32").Value
    rTier3 = Range("B32").Value
    
    tier4 = Range("A33").Value
    rTier4 = Range("B33").Value
    
    rTier5 = Range("B34").Value
    
    totalEUTransactions = totalTransactions - Range("B5").Value

    If totalTransactions <= tier1 Then
        'first tier
        Range("I5").Value = totalTransactions * rTier1
    ElseIf (totalTransactions > tier1) And (totalTransactions <= tier1 + tier2) Then
        'second tier
        Range("I5").Value = (tier1 * rTier1) + ((totalTransactions - tier1) * rTier2)
    ElseIf (totalTransactions > tier1 + tier2) And (totalTransactions <= tier1 + tier2 + tier3) Then
        'third tier
        Range("I5").Value = (tier1 * rTier1) + (tier2 * rTier2) + ((totalTransactions - (tier1 + tier2)) * rTier3)
    ElseIf (totalTransactions > tier1 + tier2 + tier3) And (totalTransaction <= tier1 + tier2 + tier3 + tier4) Then
        'fourth tier
        Range("I5").Value = (tier1 * rTier1) + (tier2 * rTier2) + (tier3 * rTier3) + ((totalTransactions - (tier1 + tier2 + tier3)) * rTier4)
    Else
        'exceeds fourth tier (5th tier)
        Range("I5").Value = (tier1 * rTier1) + (tier2 * rTier2) + (tier3 * rTier3) + (tier4 * rTier4) + ((totalTransactions - (tier1 + tier2 + tier3 + tier4)) * rTier5)
    End If
    
    'unit price
    Range("H5").Value = Range("I5").Value / Range("F5").Value

End Sub

The data this would run against would look like this:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]UK Total
[/TD]
[TD]Country2 Total
[/TD]
[TD]Country3 Total
[/TD]
[TD]Country4 Total
[/TD]
[TD]Overall Total
[/TD]
[TD]Unit Price All
[/TD]
[TD]Fee Value All
[/TD]
[TD]Unit Price EU
[/TD]
[TD]Fee Value EU
[/TD]
[/TR]
[TR]
[TD]Approvd Transactions
[/TD]
[TD]3100000
[/TD]
[TD]1500000
[/TD]
[TD]100000
[/TD]
[TD]1900000
[/TD]
[TD]6600000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Using this rate table:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Number of transactions
[/TD]
[TD]per transaction charge (p)
[/TD]
[/TR]
[TR]
[TD]up to 2000000
[/TD]
[TD]0.015
[/TD]
[/TR]
[TR]
[TD]up to 6000000
[/TD]
[TD]0.014
[/TD]
[/TR]
[TR]
[TD]up 8000000
[/TD]
[TD]0.013
[/TD]
[/TR]
[TR]
[TD]up to 10000000
[/TD]
[TD]0.012
[/TD]
[/TR]
[TR]
[TD]10000000+
[/TD]
[TD]0.011
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The sub above will happily work out the 'All' unit price and fee value.
The figures that I need to get into Unit Price EU and Fee Value EU are for the 'EU' total, which consists of the totals for country2, country3 and country4 added together.
So, the UK transactions are priced first, so that's 2000000 @ 0.015, 1100000 @ 0.014. Then come the EU transactions, which are going to be 2900000 @ 0.014 and 600000 @ 0.013.
Given that the transaction numbers will fluctuate, how can I programmatically calculate these EU Unit Price and EU Fee Value figures?
Since my human brain manages to concoct a set of instructions for doing this, I must be able to translate it into a set of instructions in VBA, but for the life of me I cannot see how to do it (staring at it for a great deal of time may have made me blind to the obvious).
If anyone can help me with this I should be eternally grateful. I'm sorry about the explanation here, please ask me for clarification on anything if required; I'm not convinced I've articulated it as well as I could.
Thanks

 

[SomeGuy84]

Also I forgot to mention, this is in Excel 2010 on Windows 7!

 

[SomeGuy84]

Any clues anyone?

 

[SomeGuy84]

Ok, I worked this out. Definitely an element of me being stupid, as it happens.
First I came up with a function for calculating the tiering with a number of transactions as a parameter, which basically goes like this

For the first tier:

(MIN(tierthreshold, MAX(0, totalTransactions)) * tierprice

Added to this, for each of the next tiers:

(MIN(tierthreshold, MAX(0, totalTransactions - prevTierThreshold)) * tierprice

And then added to, for the final tier, just the max bit

MAX(0, totalTransactions - prevThreshold) * tierprice

Then with that function I can give it first the total transactions, then just the uk transactions. The difference between them is the subset.
The subset itself is actually split into further groups, and the same principle applies, so I can give it the uk number, plus the number for the first group, and then remove that from the total, and so on.

Might help someone in the future. Tiered pricing is a pain.

oh and in vba you have to use Application.WorksheetFunction.Max or Min, just calling Max or Min in the code will get you ye olde "Sub or Function not defined" error.