Solved

Help writing a formula to match this chart

Posted on 2010-09-03
18
349 Views
Last Modified: 2012-05-10
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?
0
Comment
Question by:Rouchie
  • 9
  • 8
18 Comments
 
LVL 12

Assisted Solution

by:FDiskWizard
FDiskWizard earned 55 total points
Comment Utility
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...
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 445 total points
Comment Utility
something like this

y = 5000 / x
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 445 total points
Comment Utility
better one

y = 1 + 2 * ln (5000/x)
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
result of above forlmula
10	13.4292162

20	12.04292184

30	11.23199162

40	10.65662747

50	10.21034037

60	9.845697258

70	9.537395899

80	9.270333113

90	9.034767042

100	8.824046011

200	7.43775165

300	6.626821434

400	6.051457289

500	5.605170186

600	5.240527072

700	4.932225713

800	4.665162927

900	4.429596856

1000	4.218875825

2000	2.832581464

3000	2.021651248

4000	1.446287103

5000	1

Open in new window

0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 445 total points
Comment Utility
if you use

y = 1 + 1.448 * ln (5000/x)

looks liek your graph ;)
10	10.00

20	9.00

30	8.41

40	7.99

50	7.67

60	7.40

70	7.18

80	6.99

90	6.82

100	6.66

200	5.66

300	5.07

400	4.66

500	4.33

600	4.07

700	3.85

800	3.65

900	3.48

1000	3.33

2000	2.33

3000	1.74

4000	1.32

5000	1.00

Open in new window

0
 
LVL 51

Accepted Solution

by:
HainKurt earned 445 total points
Comment Utility
we can revise the formula like

y = 1 + 1.448 * ln (5000/if(x>5000, 5000, x))

to fix it to 1 after 5000 ;)
0
 
LVL 25

Author Comment

by:Rouchie
Comment Utility
Wow!  Thank you very much for sharing your wisdom, and so quickly.  I think HainKurt you've pretty much mastered exactly what I needed!
>> 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!
0
 
LVL 25

Author Comment

by:Rouchie
Comment Utility
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?
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

Open in new window

0
 
LVL 25

Author Comment

by:Rouchie
Comment Utility
Sorry should have clarified - at 5000 users the price is rather attractive!
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 445 total points
Comment Utility
i dont see where the problem is, if you look at your graph, it never drops 10$ per user...
if you want to share 5000 to users you can use xy=5000 formula ;)

or

y = if(x<10, 500, if(x<5000, 5000/x, 1))

ie

y = 500  if x<10
y = 5000/x if x between 10 & 5000
y = 1 if x>5000
0
 
LVL 25

Author Comment

by:Rouchie
Comment Utility
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

Open in new window

0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 445 total points
Comment Utility
if you use xy=5000 of course all the sums will be 5000 ;) we discussed 3 formulas so far...

1.
y = 1 + 1.448 * ln (5000/x)

2.
y = 500  if x<10
y = 5000/x if x between 10 & 5000
y = 1 if x>5000

3.
xy=5000 or y=5000/x
0
 
LVL 25

Author Comment

by:Rouchie
Comment Utility
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.
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 445 total points
Comment Utility
what about this

y=25/POWER(LN(x),1.3)

you can play with contstants in this formula...
10	8.45	84.54

20	6.00	120.09

30	5.09	152.74

40	4.58	183.25

50	4.24	212.22

60	4.00	240.02

70	3.81	266.89

80	3.66	292.99

90	3.54	318.44

100	3.43	343.34

200	2.86	572.26

300	2.60	779.92

400	2.44	975.45

500	2.33	1162.71

600	2.24	1343.78

700	2.17	1519.96

800	2.12	1692.12

900	2.07	1860.90

1000	2.03	2026.76

2000	1.79	3579.69

3000	1.67	5018.74

4000	1.60	6391.51

5000	1.54	7718.35

10000	1.39	13943.82

20000	1.27	25377.30

30000	1.20	36131.20

40000	1.16	46481.66

50000	1.13	56549.16

60000	1.11	66400.75

70000	1.09	76078.92

80000	1.07	85612.82

90000	1.06	95023.64

100000	1.04	104327.45

Open in new window

0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 445 total points
Comment Utility
flat?

y2-y1/x2-x1 = m
10-1 / 5000-0 = m
1/500 ~ m

so y = x/500

can you please give a better sample with values... create an excel table, with x/y values and put the numbers... if you wish add agraph too... so I can try a better formula...
0
 
LVL 25

Author Comment

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

Thanks again.
0
 
LVL 25

Author Comment

by:Rouchie
Comment Utility
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?
0
 
LVL 25

Author Closing Comment

by:Rouchie
Comment Utility
Thanks very much.  I need some time to play with all this information now... :-)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Have you ever thought of installing a power system that generates solar electricity to power your house? Some may say yes, while others may tell me no. But have you noticed that people around you are now considering installing such systems in their …
We are taking giant steps in technological advances in the field of wireless telephony. At just 10 years since the advent of smartphones, it is crucial to examine the benefits and disadvantages that have been report to us.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now