I was recently thinking a lot about tiered pricing of items. I spend a lot of time in the Excel help files and googling about my issue.

My specific problem: I wanted to evaluate many different pricing schedules. I had a lot of clients and I wanted to evaluate the impact of each pricing schedule on each client.

I could have done it with a lot of if statements in a lot of columns. The problem is that is not dynamic enough. I didn't know how many tiers I would need. Plus, it isn't just finding the right tier and applying that price. I also needed to price incremental units on the next tiers. For example, you buy 1000 units. The first 100 cost $2, the next $100 cost $1 and anything about cost $0.50. So, each additional unit lowers your average unit cost.

Anyways, I did it by writing a function in excel and have created an add-in that you can use. Download it here. Code follows:

Function TieredPrice(volume As Double, tiers As Variant, prices As Variant) As Double

TieredPrice = 0

If IsObject(prices) = True Then

If TypeOf prices Is Excel.Range Then

If prices.Rows.Count > 1 And prices.Columns.Count > 1 Then

TieredPrice = CVErr(xlErrRef)

Exit Function

End If

If prices.Rows.Count > 1 Then

NumCells = prices.Rows.Count

Else

NumCells = prices.Columns.Count

End If

pricesUB = NumCells

Else

TieredPrice = CVErr(xlErrRef)

Exit Function

End If

End If

If IsObject(tiers) = True Then

If TypeOf tiers Is Excel.Range Then

If tiers.Rows.Count > 1 And tiers.Columns.Count > 1 Then

TieredPrice = CVErr(xlErrRef)

Exit Function

End If

If tiers.Rows.Count > 1 Then

NumCells = tiers.Rows.Count

Else

NumCells = tiers.Columns.Count

End If

tiersUB = NumCells

Else

TieredPrice = CVErr(xlErrRef)

Exit Function

End If

End If

If tiersUB = pricesUB Then

If volume < tiers(1) Then

fee = volume * prices(1)

Else

fee = tiers(1) * prices(1)

End If

For i = 2 To pricesUB

If (volume - tiers(i - 1) < tiers(i) - tiers(i - 1)) Then

If (volume - tiers(i - 1)) < 0 Then

fee = fee

Else

fee = fee + (volume - tiers(i - 1)) * prices(i)

End If

Else

If (tiers(i) - tiers(i - 1)) < 0 Then

fee = fee

Else

fee = fee + (tiers(i) - tiers(i - 1)) * prices(i)

End If

End If

Next i

TieredPrice = fee / volume

Else

TieredPrice = CVErr(xlErrRef)

Exit Function

End If

End Function