# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  > [SOLVED] Formula for tiered pricing

## Unkilj

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.

----------


## Glenn Kennedy

Hi there & welcome to the Excel Forum.  The easiest way to do this is with SUMPRODUCT.  Assuming that you enter the target no. of emails in A2, this will calculate the result that you want:

=SUMPRODUCT(--(A2>{0,10000,510000,1510000}),--(A2-{0,10000,510000,1510000}),{0,0.0005,-0.00015,-0.0002})

----------


## Unkilj

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

----------


## Glenn Kennedy

You're welcome...

----------


## Glenn Kennedy

...and thanks for the Reputation.

----------


## Unkilj

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

----------


## rynoburgy

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!

----------


## 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})

----------


## rynoburgy

> 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?

----------


## Glenn Kennedy

Your description was a bit lacking in detail.  I had assumed that the first 500 kg cost 450, 500-5000 cost 420, etc.

try this

----------


## 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

----------


## rynoburgy

> 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!!!

----------


## alewis1983

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.

----------


## FDibbins

alewis1983 welcome to the forum  :Smilie: 

Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------


## alewis1983

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!

----------


## cbolin

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

----------


## cbolin

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.

----------


## AliGW

Cbolin - unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

Thread closed.

----------

