Tiered pricing formula, starting at a specific point

[benafaulkner]

Hi

I've seen other threads which solve a tiered pricing formula where the calculation starts at 0 (i.e. 0 - 10 at x price, 11 to 20 at y price, etc.) using SUMPRODUCT. However, I need a formula where you can start from a variable starting point. For example:

Standard pricing table:
0-1,000 credits at £2.50 per credit
1,001-5,000 at £2.00
5,001-10,000 at £1.40
10,001-50,000 at £1.10
50,001-100,000 at £0.90
100,001+ at £0.80

I then want to say, a customer buys 60,000 credits up front, therefore creating a starting point in the tiering at the 50,001-100,000, and enabling them to have their first 60,000 credits billed at £0.90 for committing up front. When they use up their 60,000 credits, it means they can immediately be billed at the £0.90 tier, for up to another 40,000 credits, then when they have used over 100,000 they'll move to the £0.80 tier.

I want to create an illustration where I can a) update the prices per tier and then any array would self-update (is this even possible), and b) update the starting point so that, in theory a customer could start at any point in the pricing structure (say at 10,000 so first 10,000 are at £1.40 then they'll be billed the next 40,000 at £1.10, etc.)

Can anyone please help?

Thanks

[63falcondude]

I am assuming that your example of 60,000 credits should return 54,000.

With your lower values in A2:A7, your prices in C2:C7, and the credits in E2, you can use this:

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

You can also format A2:C7 as a table then use table ranges in the formula. This will ensure that any extra rows added later are accounted for in the formula.

[benafaulkner]

Thanks 63falcondude, but I've perhaps not been very clear. I need to be able to create a sheet which, if once all the variables above are entered, say the customer uses 85,000 credits would calculate that the first 60,000 we at £0.90 (thus £54,000), the next 25,000 were then also billed at £0.90, so an additional £22,500. Or if they had used 110,000, the first 60,000 would be at £0.90, the next 40,000 also at £0.90 and the final 10,000 would be at £0.80 thus the total would be £54,000+£36,000+£8000=£98,000.

[63falcondude]

Post #1:

I then want to say, a customer buys 60,000 credits up front, therefore creating a starting point in the tiering at the 50,001-100,000

Post #3:

Or if they had used 110,000, the first 60,000 would be at £0.90

Based on post #1, if a customer buys 110,000 credits, shouldn't they start in the 100,001+ bracket?
What tells you to start at the 50,001-100,000 bracket if a customer buys 110,000?

[benafaulkner]

Again - apologies for the lack of clarity! I'm trying to build a model/illustration so I can change certain variables. The problem is we don't know how many a customer will use - so if they commit to a minimum, their pricing starts at that tier. Then they are billed for that minimum (in this case 60,000) and then the pricing of usage over that min needs to follow the tiering. The idea is then we could use the same illustration and change the minimum to say, 20,000, so they'd be charged £1.10 x 20,000 upfront, and then pay as per the tiering after their first 20,000. Is that any clearer? :S

[Pauleyb]

No, solution, just hoping to clarify.

The usual response would be to use a formula like this:
=SUMPRODUCT((A26-{0,1000,5000,10000,50000,100000})*(A26>{0,1000,5000,10000,50000,100000}),{2.5,-0.5,-0.6,-0.3,-0.2,-0.1})

But you want a formula that takes into account a prepurchase, so, if a person prepurchased 40000 credits, you would want the formula to convert to:
=SUMPRODUCT((A26-{0,1000,5000,10000,50000,100000})*(A26>{0,1000,5000,10000,50000,100000}),{1.1,0,0,0,-0.2,-0.1})

Is this correct?

Unfortunately, you can't put formulas into the {} arrays.

Pauley

[63falcondude]

My logic might be flawed here but I am thinking that we might be able to use the classic tiered SUMPRODUCT formula (from 0 to the actual) and subtract from that another tiered SUMPRODUCT formula (from 0 to the min). Theoretically, that should leave us with a tiered formula calculating from the minimum to the actual.

Note that this would not be dynamic.

It would be helpful if you would give us a few more examples including minimum (starting point), actual, and the desired results for each.

[benafaulkner]

No, solution, just hoping to clarify ...

Pauley

Thanks, and A26 would be the total usage right? So it looks like you've just hardcoded the fact that 40,000 was prepurchased in the last set of the array formula. Is that right? Ideally I don't want to hard code as I want sale people to be able to change the prepurchase figure... Any other ideas? Perhaps it actually needs to be a series of formulae?

[benafaulkner]

My logic might be flawed here but I am thinking that we might be able to use the classic tiered SUMPRODUCT formula (from 0 to the actual) and subtract from that another tiered SUMPRODUCT formula (from 0 to the min). Theoretically, that should leave us with a tiered formula calculating from the minimum to the actual.

Note that this would not be dynamic.

It would be helpful if you would give us a few more examples including minimum (starting point), actual, and the desired results for each.

Thanks: here goes:

a) prepurchase of 30,000, usage of 60,000 = first 30,000 at £1.10, next 20,000 at £1.10 and following 10,000 at next tier of £0.90 = £64,000
b) prepurchase of 7,000, usage of 11,000 = first 7,000 at £1.40, next 3,000 at £1.40, following 1,000 at £1.10 = £15,100

Helpful? Basically, the prepurchase bypasses the lower tiers up to the prepurchase number...

[AliGW]

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

[benafaulkner]

Sorry! I'm fairly new to this - I thought the quotes were helpful, but I get it

[AliGW]

They can be, as explained in post #10, but more often than not are just clutter.

[63falcondude]

With your data in A1:C7, try this:

Create two helper columns (which can both be heidden) using these formulas:

D2 =IF(A2<G$2,0,A2)

E2 =IF(B2>G$2,IF(H$2<D2,0,IF(H$2>D3,D3-D2,H$2-D2)),0)

E7 =IF(B7>G$2,IF(H$2<D7,0,H$2-D7),0)

Then, with your prepurchase in G2 and your total usage in H2, use this for the total cost:

=SUMPRODUCT(E2:E7,C2:C7)

This allows you to change the bounds, price per credit, prepurchase, and usage.

This works for all of the samples that you have shared thus far. See attachment.

[Pauleyb]

@benafalkner: Yes, I understand you would not want the equation to be hard-coded, but to adjust for the 'input' in A26 (in my formula). I just wanted to ensure I was looking at it the correct way before digging in any further. If @63falcondude's response is sufficient, then let us know, but I am thinking you want a 'simple' formula that would change with the prepurchase and with the current amount in use.

Pauley

[Pauleyb]

Using @63falcondude's spreadsheet (using the table and the input cells) this equation works:

Please Login or Register  to view this content.

This is essentially the amount paid for the prepurchase plus the difference between the price of the total usage without credits and what the price would have been for the prepurchase without the discount.

This does not work if Total Usage is less than the pre-purchase, but a simple IF() clause around the sumproduct equations can fix that.

[benafaulkner]

@63falcondude Thanks so much! This is exactly what I needed - and let's me change the variables, and forecast monthly based on cumulative usage etc, so is perfect!
Thanks all!

[AliGW]

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

[63falcondude]

You're welcome. Happy to help.