+ Reply to Thread
Results 1 to 5 of 5

Formula for tiered pricing

Hybrid View

riaface Formula for tiered pricing 10-16-2014, 11:27 AM
ben_hensel Re: Formula for tiered pricing 10-16-2014, 11:31 AM
FDibbins Re: Formula for tiered pricing 10-16-2014, 11:33 AM
riaface Re: Formula for tiered pricing 10-16-2014, 11:37 AM
riaface Re: Formula for tiered pricing 10-16-2014, 12:38 PM
  1. #1
    Registered User
    Join Date
    10-16-2014
    Location
    UK
    MS-Off Ver
    Mac 2011
    Posts
    3

    Formula for tiered pricing

    Hello,

    I need to calculate spend based on the number of emails I send a month. The emails are priced using the below grid:

    Up to 12k emails per month: Free
    Next 1m emails per month (per thousand emails): $0.20
    Next 5m emails per month (per thousand emails): $0.15
    Remaining emails (per thousand): $0.10

    So if I was sending 10,000,000 emails, it would work out like this:
    TIER SENDS COST
    Free 12,000 $0.00
    $0.20 per thousand tier (next 1m) 1,000,000 - $200.00
    $0.15 per thousand tier (next 5m) 5,000,000 - $750.00
    $0.10 per thousand tier (remaining emails) 3,988,000 - $398.80
    MONTHLY TOTAL 10,000,000 $1,348.8

    Is there a single line formula I could use to calculate this? So number of emails, for example, is in A1, and I want the cost to show in A2.

    Thanks!

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Formula for tiered pricing

    I'm trying to think of a business application for sending out millions of emails a month that I wouldn't mind providing free help to.

    Are you a spammer?
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Formula for tiered pricing

    wow my thoughts exactly, Ben. who on earth would you be sending 10 million emails to - that would actually want to receive them?????
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    10-16-2014
    Location
    UK
    MS-Off Ver
    Mac 2011
    Posts
    3

    Re: Formula for tiered pricing

    Not a spammer - they're notification emails for an app, rather than emails from a person!

    If it's at all helpful, we're using Mandrill: http://mandrill.com/pricing/

  5. #5
    Registered User
    Join Date
    10-16-2014
    Location
    UK
    MS-Off Ver
    Mac 2011
    Posts
    3

    Re: Formula for tiered pricing

    Someone on another forum managed to solve this for me, so I thought I'd post the answer here in case anyone else needs something similar.

    =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)))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 01-11-2014, 01:47 PM
  2. HELP required - cannot create formula to auto calculate tiered pricing...
    By dcj1606 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-18-2013, 05:16 PM
  3. [SOLVED] Tiered Pricing
    By unpuertomex in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-27-2012, 09:32 PM
  4. Tiered pricing
    By jimbojones82 in forum Excel General
    Replies: 1
    Last Post: 07-24-2012, 09:25 AM
  5. Tiered Pricing Question
    By tgrant in forum Excel General
    Replies: 4
    Last Post: 12-03-2010, 05:50 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1