Excel Formula For Fixed amount sliding scale amount

I want to calculate the total amount of monthly fees based on the requirement price schedule below.

Price Schedule* – Monthly Fees per End User

1-100 end users: $29 each
101 - 250 end users: $27 each
251 - 500 end users: $25 each
501 - 750 end users: $23 each
750 - 1000 end users: $21 each
1001 - 1500 end users: $19 each
1501 - 2000 end users: $15 each
2001 - 2500 end users: $11 each
2501 - 5000 end users: $9 each
5001+ end users: $7 each

*The prices apply to each customer in their respective tier. For clarity, the fee for the first 100 end users remains at $29 regardless of the total number of end users. The fee for the 101st to 250th end user is $27, and so on.

The input would be the number of end-users and the output would be the total monthly fees

An example would be 150 end users and the output would be:

(100*29) + (49*27)
= 4,223

It is easy enough to manually calculate but really need a formula that is smart enough to calculate given just the number of end users

2 Answers

What I ended up doing was this formula. Not sure if this is the best way but seems to work

=(MIN(B1,100)*29)+
MAX(0,(MIN(250-100,(B1-100))*27))+
MAX(0,(MIN(500-250,(B1-250))*25))+
MAX(0,(MIN(750-500,(B1-500))*23))+
MAX(0,(MIN(1000-750,(B1-750))*21))+
MAX(0,(MIN(1500-1000,(B1-1000))*19))+
MAX(0,(MIN(2000-1500,(B1-1500))*15))+
MAX(0,(MIN(2500-2000,(B1-2000))*11))+
MAX(0,(MIN(5000-2500,(B1-2500))*9))+
MAX(0,(B1-5000)*7)

This sort of issue is usually best served by a lookup table and the VLOOKUP function.

It allows for easier maintenance if things might change in the future.

For example, set up your monthlyTable like this:

The first and third columns are entered manually. The Base column is computed with the formula:

H2: 0
H3: =(G3-G2)*I2+H2

Then you can use the formula:

B2: =VLOOKUP(A2,monthlyFees,2)+(A2-VLOOKUP(A2,monthlyFees,1))*VLOOKUP(A2,monthlyFees,3)