Rate Table Calculation

J

J03xcel

New Member
Joined
Dec 20, 2013
Messages
10
Hello,

I am trying to compare the calculated prices for different rate tables in our billing system. Below are 2 examples:

Table 1
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Tier[/TD]
[TD]From[/TD]
[TD]To[/TD]
[TD]Base[/TD]
[TD]Per[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]$100[/TD]
[TD]$2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]50[/TD]
[TD]0[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]51[/TD]
[TD]9999[/TD]
[TD]0[/TD]
[TD]$.5[/TD]
[/TR]
</tbody>[/TABLE]

Table 2
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Tier[/TD]
[TD]From[/TD]
[TD]To[/TD]
[TD]Base[/TD]
[TD]Per[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]$30[/TD]
[TD]$5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]$25[/TD]
[TD]$4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]11[/TD]
[TD]50[/TD]
[TD]$15[/TD]
[TD]$3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]51[/TD]
[TD]100[/TD]
[TD]$5[/TD]
[TD]$2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]101[/TD]
[TD]9999[/TD]
[TD]0[/TD]
[TD]$1[/TD]
[/TR]
</tbody>[/TABLE]

Definitions:
  • From, beginning quantity greater than or equal to
  • To, ending quantity less than or equal to
  • Base, flat price added to total
  • Per, price per

Calculated prices are cumulative across different tiers. Example: if the quantity is 50, the calculated prices would be $151 (100 + 2(1) + 1(49)) for Table 1, and $205 (30 + 5(5) + 25 + 4(5) + 15 + 3(30)) for Table 2.

When exported from the billing system, the CSV flattens each rate table into a single row:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Table[/TD]
[TD]From(1)[/TD]
[TD]To(1)[/TD]
[TD]Base(1)[/TD]
[TD]Per(1)[/TD]
[TD]From(2)[/TD]
[TD]To(2)[/TD]
[TD]Base(2)[/TD]
[TD]Per(2)[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]100[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]50[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]30[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]25[/TD]
[TD]4[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]

Assumptions:
  • Rate tables can have 1-30 tiers
  • Approx 8,000 rate tables
  • Tiers are identified in the column headings, i.e. From(2) is Tier 2.

Ideally, I would like to insert a Calculation column which for a given quantity would calculate the price for all rate tables. Any ideas?

Thanks!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
One way, using a data table:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
A​
[/td][td="bgcolor:#C0C0C0"]
B​
[/td][td="bgcolor:#C0C0C0"]
C​
[/td][td="bgcolor:#C0C0C0"]
D​
[/td][td="bgcolor:#C0C0C0"]
E​
[/td][td="bgcolor:#C0C0C0"]
F​
[/td][/tr][tr][td="bgcolor:#C0C0C0"]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]
Qty​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
2​
[/td][td][/td][td][/td][td][/td][td][/td][td]
50​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
3​
[/td][td="bgcolor:#F3F3F3"]
From​
[/td][td="bgcolor:#F3F3F3"]
To​
[/td][td="bgcolor:#F3F3F3"]
Base​
[/td][td="bgcolor:#F3F3F3"]
Per​
[/td][td="bgcolor:#F3F3F3"]
Cost​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
4​
[/td][td]
1​
[/td][td]
5​
[/td][td]
$30.00​
[/td][td]
$5.00​
[/td][td="bgcolor:#E5E5E5"]
$55.00​
[/td][td]E4: =IF($E$2 >= A4, C4 + MAX(0, MIN(B4 - A4 + 1, $E$2 - A4 + 1)) * D4, 0)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
5​
[/td][td]
6​
[/td][td]
10​
[/td][td]
$25.00​
[/td][td]
$4.00​
[/td][td="bgcolor:#E5E5E5"]
$45.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
6​
[/td][td]
11​
[/td][td]
50​
[/td][td]
$15.00​
[/td][td]
$3.00​
[/td][td="bgcolor:#E5E5E5"]
$135.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
7​
[/td][td]
51​
[/td][td]
100​
[/td][td]
$5.00​
[/td][td]
$2.00​
[/td][td="bgcolor:#E5E5E5"]
$0.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
8​
[/td][td]
101​
[/td][td]
9999​
[/td][td]
$0.00​
[/td][td]
$1.00​
[/td][td="bgcolor:#E5E5E5"]
$0.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
9​
[/td][td][/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]
Qty​
[/td][td="bgcolor:#CCFFCC"]
$235.00 ​
[/td][td]E9: =SUM(E4:E8)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
10​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][td="bgcolor:#E5E5E5"]
$35.00​
[/td][td]E10:E19: {=TABLE(,E2)}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
11​
[/td][td][/td][td][/td][td][/td][td]
2​
[/td][td="bgcolor:#E5E5E5"]
$40.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
12​
[/td][td][/td][td][/td][td][/td][td]
5​
[/td][td="bgcolor:#E5E5E5"]
$55.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
13​
[/td][td][/td][td][/td][td][/td][td]
6​
[/td][td="bgcolor:#E5E5E5"]
$84.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
14​
[/td][td][/td][td][/td][td][/td][td]
10​
[/td][td="bgcolor:#E5E5E5"]
$100.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
15​
[/td][td][/td][td][/td][td][/td][td]
11​
[/td][td="bgcolor:#E5E5E5"]
$118.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
16​
[/td][td][/td][td][/td][td][/td][td]
50​
[/td][td="bgcolor:#E5E5E5"]
$235.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
17​
[/td][td][/td][td][/td][td][/td][td]
51​
[/td][td="bgcolor:#E5E5E5"]
$242.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
18​
[/td][td][/td][td][/td][td][/td][td]
100​
[/td][td="bgcolor:#E5E5E5"]
$340.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
19​
[/td][td][/td][td][/td][td][/td][td]
101​
[/td][td="bgcolor:#E5E5E5"]
$341.00​
[/td][td][/td][/tr]
[/table]

I would say that is flawed as a pricing method; why should the sixth unit cost nearly 6 times as much as the fifth?
 
Last edited:
Upvote 0
A little simplification:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
A​
[/td][td="bgcolor:#C0C0C0"]
B​
[/td][td="bgcolor:#C0C0C0"]
C​
[/td][td="bgcolor:#C0C0C0"]
D​
[/td][td="bgcolor:#C0C0C0"]
E​
[/td][td="bgcolor:#C0C0C0"]
F​
[/td][/tr][tr][td="bgcolor:#C0C0C0"]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]
Qty​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
2​
[/td][td][/td][td][/td][td][/td][td][/td][td]
50​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
3​
[/td][td="bgcolor:#F3F3F3"]
From​
[/td][td="bgcolor:#F3F3F3"]
To​
[/td][td="bgcolor:#F3F3F3"]
Base​
[/td][td="bgcolor:#F3F3F3"]
Per​
[/td][td="bgcolor:#F3F3F3"]
Cost​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
4​
[/td][td]
1​
[/td][td]
5​
[/td][td]
$30.00​
[/td][td]
$5.00​
[/td][td="bgcolor:#E5E5E5"]
$55.00​
[/td][td]E4: =($E$2 >= A4) * (C4 + MAX(0, MIN($E$2, B4) - A4 + 1) * D4)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
5​
[/td][td]
6​
[/td][td]
10​
[/td][td]
$25.00​
[/td][td]
$4.00​
[/td][td="bgcolor:#E5E5E5"]
$45.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
6​
[/td][td]
11​
[/td][td]
50​
[/td][td]
$15.00​
[/td][td]
$3.00​
[/td][td="bgcolor:#E5E5E5"]
$135.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
7​
[/td][td]
51​
[/td][td]
100​
[/td][td]
$5.00​
[/td][td]
$2.00​
[/td][td="bgcolor:#E5E5E5"]
$0.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
8​
[/td][td]
101​
[/td][td]
9999​
[/td][td]
$0.00​
[/td][td]
$1.00​
[/td][td="bgcolor:#E5E5E5"]
$0.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
9​
[/td][td][/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]
Qty​
[/td][td="bgcolor:#CCFFCC"]
$235.00 ​
[/td][td]E9: =SUM(E4:E8)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
10​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][td="bgcolor:#E5E5E5"]
$35.00​
[/td][td]E10:E19: {=TABLE(,E2)}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
11​
[/td][td][/td][td][/td][td][/td][td]
2​
[/td][td="bgcolor:#E5E5E5"]
$40.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
12​
[/td][td][/td][td][/td][td][/td][td]
5​
[/td][td="bgcolor:#E5E5E5"]
$55.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
13​
[/td][td][/td][td][/td][td][/td][td]
6​
[/td][td="bgcolor:#E5E5E5"]
$84.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
14​
[/td][td][/td][td][/td][td][/td][td]
10​
[/td][td="bgcolor:#E5E5E5"]
$100.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
15​
[/td][td][/td][td][/td][td][/td][td]
11​
[/td][td="bgcolor:#E5E5E5"]
$118.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
16​
[/td][td][/td][td][/td][td][/td][td]
50​
[/td][td="bgcolor:#E5E5E5"]
$235.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
17​
[/td][td][/td][td][/td][td][/td][td]
51​
[/td][td="bgcolor:#E5E5E5"]
$242.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
18​
[/td][td][/td][td][/td][td][/td][td]
100​
[/td][td="bgcolor:#E5E5E5"]
$340.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
19​
[/td][td][/td][td][/td][td][/td][td]
101​
[/td][td="bgcolor:#E5E5E5"]
$341.00​
[/td][td][/td][/tr]
[/table]

To insert the data table, select D9:E19, Data > What-If Analysis > Data Table, Column input cell: E2
 
Upvote 0
Thanks for the reply, but this does not address my issue as each billing table when exported is flatted into a single row (see first post). I could write a giant nested-if statement, but wasn't sure if there was a more efficient way.
 
Last edited:
Upvote 0
'Twere me, I would write some code to change the flat representation back to a table and proceed from there.

Or ask the billing department to export in a less hostile format.
 
Upvote 0
Ended up writing it in VBA. It assumes the quantity to be calculated is in cell A1.


Code: 
Dim intEmployees, remainingEmployees, tierEmployees As IntegerDim rangeCalculated As Range
Dim rangeRateTable As Range
Dim i, j, rateTier, maxTier As Integer
Dim mCell, mCell2
Dim mRow, mRow2
Dim mColumn
Dim currentRow As Integer
Dim arrayRateTable(1 To 92)
Dim calcAmount
Dim intFrom, intTo, intBase, intPer


Sub calculateTotal()
intEmployees = Sheet2.Range("A1").Value
remainingEmployees = Sheet2.Range("A1").Value
i = 2
Set rangeCalculated = Sheet2.Range("A2:A" & Sheet2.Range("B1").End(xlDown).Row)


For Each mCell In rangeCalculated
    mRow = mCell.Row
    mColumn = Sheet2.Range("B" & mRow).End(xlToRight).Column
    
    
    'validate number of columns
    If 4 - ((mColumn - 1) * (4 / (mColumn - 1))) = 0 Then
        Set rangeRateTable = Sheet2.Range("B" & mRow & ":" & Sheet2.Cells(mRow, mColumn).Address)
        j = 0
        
        'build rate table
        For Each mCell2 In rangeRateTable
            j = j + 1
            arrayRateTable(j) = mCell2
        Next mCell2
    Else
        calcAmount = "error"
    End If
    
    
    'set standard values for calculation
    rateTier = 1
    maxTier = j / 4
    intFrom = -3
    intTo = -2
    intBase = -1
    intPer = 0
    
    'loop through rate table to calculate amount
    Do While rateTier <= maxTier
        intFrom = intFrom + 4
        intTo = intTo + 4
        intBase = intBase + 4
        intPer = intPer + 4
        If intEmployees >= arrayRateTable(intFrom) And intEmployees <= arrayRateTable(intTo) Then
            calcAmount = calcAmount + arrayRateTable(intBase)
            calcAmount = calcAmount + (intEmployees - arrayRateTable(intFrom) + 1) * arrayRateTable(intPer)
        ElseIf intEmployees >= arrayRateTable(intFrom) And intEmployees > arrayRateTable(intTo) Then
            calcAmount = calcAmount + arrayRateTable(intBase)
            calcAmount = calcAmount + (arrayRateTable(intTo) - arrayRateTable(intFrom) + 1) * arrayRateTable(intPer)
            remainingEmployees = remainingEmployees - (arrayRateTable(intTo) - arrayRateTable(intFrom)) - 1
        End If


        rateTier = rateTier + 1
    Loop
    
    'add value to cell
    Sheet2.Range("A" & mRow).Value = calcAmount
    
    'reset values
    remainingEmployees = intEmployees
    calcAmount = 0
    Erase arrayRateTable
Next mCell






End Sub
 
Last edited:
Upvote 0
Good job, glad you got it sorted.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Water Bill Calculator w/ROI from Percentage Savings
Replies
1
Views
395
Jared016
J
L
  • Question Question
Average cost on a tiered pricing table
Replies
2
Views
257
Dave Patton
D
E
  • Solved
Performance issue using a combination of dropdowns & lookups
Replies
9
Views
512
Akuini
Akuini
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
154
krunal123
K
J
  • Question Question
Actual loan rate with fees calculation
Replies
6
Views
485
Dave Patton
D

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top