Rouchie

asked on

# Help writing a formula to match this chart

Before I start, yes I do wish I'd paid more attention in math's class...! ;-)

I need to create a formula to sell a product based on the number of people using it.

So, if only 10 people in a company use it, then the company pays a maximum of, say, $10 per user. However, at 5000 users they should pay a lot less per-user, say, $1 per user. The maximum should be $10 per user and the minimum should be $1 per user (for 10 users and 5000 users respectively). If a company has 10,000 users they should still pay $1 per user.

I have included a chart to hopefully simplify this:

Can anyone help devise a formula that would give me a price per user based on the number of users entered?

I need to create a formula to sell a product based on the number of people using it.

So, if only 10 people in a company use it, then the company pays a maximum of, say, $10 per user. However, at 5000 users they should pay a lot less per-user, say, $1 per user. The maximum should be $10 per user and the minimum should be $1 per user (for 10 users and 5000 users respectively). If a company has 10,000 users they should still pay $1 per user.

I have included a chart to hopefully simplify this:

Can anyone help devise a formula that would give me a price per user based on the number of users entered?

SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

Wow! Thank you very much for sharing your wisdom, and so quickly. I think HainKurt you've pretty much mastered exactly what I needed!

If you could provide this for me as well that would be really useful. At least then I could plot both into Excel and compare the results for different numbers of users.

Thanks again!

*>> Do you want it on a curve like that, so that for instance 700 users would be around 3$?**Or would it be better to have 11-500, 501-1000, etc...*If you could provide this for me as well that would be really useful. At least then I could plot both into Excel and compare the results for different numbers of users.

Thanks again!

ASKER

Hang on there's a problem (I think!).

When I plot this into Excel, at a certain point the cost per user plummets (see code). Is there a way to prevent this happening?

When I plot this into Excel, at a certain point the cost per user plummets (see code). Is there a way to prevent this happening?

```
Users Price Per User Total
5 £10.08 £50.41
10 £10.07 £100.73
15 £10.06 £150.95
50 £10.00 £500.00
100 £9.91 £990.91
150 £9.82 £1,472.73
250 £9.64 £2,409.09
350 £9.45 £3,309.09
500 £9.18 £4,590.91
750 £8.73 £6,545.45
1000 £8.27 £8,272.73
4000 £2.82 £11,272.73
5000 £1.00 £5,000.00
```

ASKER

Sorry should have clarified - at 5000 users the price is rather attractive!

SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

When I plot this into Excel it gives me the following. Have I done something wrong because all the totals are the same?

```
Users Price Per User Total
10 £500.00 £5,000.00
15 £333.33 £5,000.00
50 £100.00 £5,000.00
100 £50.00 £5,000.00
150 £33.33 £5,000.00
250 £20.00 £5,000.00
350 £14.29 £5,000.00
500 £10.00 £5,000.00
750 £6.67 £5,000.00
1,000 £5.00 £5,000.00
4,000 £1.25 £5,000.00
5,000 £1.00 £5,000.00
```

SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

After plotting your different proposals into Excel, I don't think a curved reduction on the cost-per-user is going to work for us. Its important that the overall total always increases with the number of users, and that the cost-per-user decreases with the number of users also.

Can your proposals be tweaked to give a flat reduction instead of curved (e.g. the cost per user decreases directly in proportion to the number of users)?

Thanks for your help so far btw.

Can your proposals be tweaked to give a flat reduction instead of curved (e.g. the cost per user decreases directly in proportion to the number of users)?

Thanks for your help so far btw.

SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

Okay no problem - please give me some time to gather people's opinions and I'll get a spreadsheet uploaded...

Thanks again.

Thanks again.

ASKER

Hi HainKurt

The problem at the moment is that we don't actually know exactly what price we want each user figure to be. We have decided on the upper and lower price boundaries, and just want to see what the intermediary values are using whatever proposal is chosen from here.

The reason for my original post was to get a rough formula that we could then graph, and tweak as needed to give the best outcome. Unfortunately I think your skills have made me need to revise my plans once I saw the results.

So...

We need to start off at 10 users @ $10 per user

The price must then fall until we get to 10,000 users @ $1 per user

Beyond those user total limits I can quite easily stop Excel altering the price.

What would be perfect is if the formula could have two variables that could be adjusted:

- Variable 1 to affect the decrease in price as the user numbers increase from 10 upwards

- Variable 2 to affect the decrease in price when approaching the 10,000 user figure

The two variables would therefore create a 'price curve' where either side could be altered until we were happy with the prices calculated.

Does that sound plausible?

The problem at the moment is that we don't actually know exactly what price we want each user figure to be. We have decided on the upper and lower price boundaries, and just want to see what the intermediary values are using whatever proposal is chosen from here.

The reason for my original post was to get a rough formula that we could then graph, and tweak as needed to give the best outcome. Unfortunately I think your skills have made me need to revise my plans once I saw the results.

So...

We need to start off at 10 users @ $10 per user

The price must then fall until we get to 10,000 users @ $1 per user

Beyond those user total limits I can quite easily stop Excel altering the price.

What would be perfect is if the formula could have two variables that could be adjusted:

- Variable 1 to affect the decrease in price as the user numbers increase from 10 upwards

- Variable 2 to affect the decrease in price when approaching the 10,000 user figure

The two variables would therefore create a 'price curve' where either side could be altered until we were happy with the prices calculated.

Does that sound plausible?

ASKER

Thanks very much. I need some time to play with all this information now... :-)

Open in new window