Rate Table Calculation

[J03xcel]

Hello,

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

Table 1

Tier	From	To	Base	Per
1	1	1	$100	$2
2	2	50	0	$1
3	51	9999	0	$.5

<tbody>
</tbody>

Table 2

Tier	From	To	Base	Per
1	1	5	$30	$5
2	6	10	$25	$4
3	11	50	$15	$3
4	51	100	$5	$2
5	101	9999	0	$1

<tbody>
</tbody>

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:

	A	B	C	D	E	F	G	H	I	...
1	Table	From(1)	To(1)	Base(1)	Per(1)	From(2)	To(2)	Base(2)	Per(2)	...
2	1	1	1	100	2	2	50	0	1	...
3	2	1	5	30	5	6	10	25	4	...

<tbody>
</tbody>

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!

 

[shg]

One way, using a data table:

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

I would say that is flawed as a pricing method; why should the sixth unit cost nearly 6 times as much as the fifth?

 

[shg]

A little simplification:

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

To insert the data table, select D9:E19, Data > What-If Analysis > Data Table, Column input cell: E2

 

[J03xcel]

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.

 

[shg]

'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.

 

[shg]

I would write some code ...

Or just some formulas.

 

[J03xcel]

Ended up writing it in VBA. It assumes the quantity to be calculated is in cell A1.

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

 

[shg]

Good job, glad you got it sorted.