Microsoft Excel Add-in

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