Rate per call based on tiered pricing schedule

[sarahcombs]

I have been struggling with coming up with the best way to handle a formula. I am trying to calculate, (and develop a standard template), where I can calculate the total rate per minute per call based on our tiered pricing. Here are the inputs, which could be variable:

Total calls: 350,000
Avg length of call: 2 minutes

From To Tier Rate / Minute
- 50,000 0.20
50,001 100,000 0.19
100,001 150,000 0.18
150,001 200,000 0.17
200,001 250,000 0.16
250,001 above 0.15

Played around with if statements, sumproducts but nothing is working. I would definitely like to do this so that the only things I need to change are the total calls and perhaps length of call. Any inputs are greatly appreciated.

[jeffreybrown]

Hello & Welcome to the Forum,

Is your expected answer .3 from the two variables presented?

E3 =E2*LOOKUP(E1,A2:A7,C2:C7)

Please Login or Register  to view this content.

[sarahcombs]

Hi, thanks for your reply. I think that will work....too easy! I was definitely overthinking it. Thank you!

[jeffreybrown]

You are very welcome. Happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.