Formula for tiered pricing

Hi all,

I'm new to the forum and would really appreciate some help with working out a formula for tiered/progressive pricing. There was a post on this not so long ago by riaface and he got an answer which is very close to what I need.

We are using a bulk mailing company to send out mass (they are legit before you ask) emails to our customers. The company's pricing structure is located at http://www.mailgun.com/pricing.

A summary of their pricing is as follows:
Emails Price Per Email
0**-**10,000 Free
Next 500,000 $0.00050
Next 1,000,000 $0.00035
Next 5,000,000 $0.00015
Any Additional $0.00010

The last post was from riaface was using a different company but with a very similar pricing structure located here https://mandrill.com/pricing/ - the formula that works well for this is:

=IF(AND(A1>12000,A1<=1000000),((A1-12000)/1000)*0.2,IF(AND(A1>1000000,A1<=5000000),200+((A1-1012000)/1000)*0.15,IF(A1>5000000,950+((A1-6012000)/1000)*0.1)))

Could any one of you experts help me with this before I lose my mind ;-)

Thanks in advance.

Wow, what a fantastic response! This is spot on, thanks very much Glenn - really appreciate your help with this :-)

Well deserved, it's the closest I could get to buying you a beer!

Hi, I need help with a similar formula for my Excel sheet. We are working with a manufacturer who offers product based on the quantity that we order. The following is the breakdown:

$450 1+ kilo
$420 500 kilos
$362 5100 kilos
$316 10100 kilos

I am trying to use this formula: =SUMPRODUCT(--(A2>{1,500,5100,10100}),--(A2-{1,500,5100,10100}),{450,420,362,316}) But it's not giving me the result I need. Could anyone please assist? Thanks!

Originally Posted by Glenn Kennedy

Hi. ry this. If it doesn't give the correct answer, please post some values and their expected answers.

=SUMPRODUCT(--(A2>{0,500,5000,10000}),--(A2-{0,500,5000,10000}),{450,-30,-58,-36})

Hi Glenn, thanks for the fast response!! I have tried this formula and it still isn't working properly. For example, if I input 5500 into A2, the result should be 1,991,000, due to 5500*362. But instead, when plugged into the formula it returns: 2,296,000, which is much higher. Am I missing something?

Originally Posted by Glenn Kennedy

Hi. Your explanation was far from clear. I had assumed that the first 500 kg cost 450, the next 4500 kg cost 420, etc. Try this

Sorry that was my bad! I'm new to all of this. Thank you so much for your help... the new form is perfect. You're amazing.

Thanks again!!!

Thanks for all of this brilliance Glenn Kennedy, I am new to the forum and hope to borrow some. I have read through and believe my need is closer to ry's post. I am looking for a cumulative total from the tier using sumproduct.

=SUMPRODUCT(--(C9>{0,101,501,1001,1501,2000}),--(C9-{0,101,501,1001,1501,2000}),{100,-25,-25,-25,-10,-6})
C9=Input

I am so close I can taste it thanks to you. I have broken out a manual calculation along with a breakdown of units/cost in each tier to check the formula. Any help is much appreciated!

Please see the image:Screen Shot 2016-11-03 at 9.50.44 AM.png

I cannot seem to get my manual calculation to line up with the formula. Please note the calculation and input fields.

FD - You got it, thank you. I guess I looked at RY's "new question" in UNK's original post and thought..."Efficiency!". However, I get and appreciate the benefits. I will do just that. Cheers!

0-10,000 transactions = $1,000
10,001-50,000 transactions = $5,000
50,001-150,000 transactions = $15,000
150,001-500,000 transactions = $30,000
500,001-1,000,000 transactions = $60,000

I would like to figure out a formula to where I multiply users by transactions and it displays one of the tiers

Here is their tier table
0-10,000 transactions = $1,000
10,001-50,000 transactions = $5,000
50,001-150,000 transactions = $15,000
150,001-500,000 transactions = $30,000
500,001-1,000,000 transactions = $60,000

I would like to figure out a formula to where it will multiply users by transactions and it displays one of the tiers. That way I can adjust the number of transactions and it would change the to the correct tier pricing

For example: If there is 500 users who each make 1 transaction it would sum $1,000 or if there was 500 users who each make 21 transactions it would sum $5,000.