I'm looking to build a pricing curve for an application that takes into account the following:
1 - 1000 users - $9.00/user average (so 500 users would be $9.00 each, with fewer than 500 gradually increasing in cost, and greater than 500 gradually decreasing in cost)
1001 - 10000 users - $2.50/user average
10001 - 25000 users - $1.50/user average.
The minimum we're willing to charge per user is $.15
What is the best way to do this?
Thanks in advance!
Last edited by ArtlessIbex; 03-11-2016 at 12:24 PM.
Make a VLOOKUP Table (can be on a different sheet)
1-------9
1001--2.5
10001-1.5
etc.
Then if the number of users in in C2
=C2*VLOOKUP(C2, Sheet2!$A$1:$B$10,2)
Does that help?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Wow, quick reply!
I get the gist of what you're saying, but I'm in no way an Excel expert. Once I select VLOOKUP, the function arguments come up. Can you tell me where to go from here?
See if this helps...
http://contextures.com/xlFunctions02.html
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
I assume users are in A1:An
In B1:and drag downFormula:
=IFERROR(CHOOSE(MATCH(A1,{1,1001,10001,25001,40001,65001}),9,2.5,1.5,1,0.5,0.15),"No users")
change values of users and cost values suitable
I'm working through the VLOOKUP training video. Thanks for that.
Hi Sandy666. Thanks, that is helpful because I can see what you did. However, the curve needs to take into account the sliding portion of the pricing. So if a company has 500 users, they are $9/user. If they had 250 users, the price would be a bit higher per user. If they had 750 users, the price would be a bit lower. The midpoint of each group is the number of users with that price.
1 - 1000 users averages $9 per user (so 500 users would actually cost $9/user)
1001 - 10000 users averages $2.50 per user (so 5500 users would actually cost $2.50/user)
10001 - 25000 users averages $1.50 per user (so 17500 users would actually cost $1.50/user)
I feel like this should be simpler to accomplish. I can picture in my head exactly what it needs to look like, but I can't figure out how to build it.
I genuinely appreciate all the assistance.
So define all ranges with users and costs.
1-250 =?
251-500 =?
501-1000 =?
etc.
Excel doesn't know what you've in your head so you need define it
Ahh, so it's a linear sliding scale. That changes everything. The VLOOKUP won't help (at least not directly)
I don't think you've given enough information. At least for the first curve, we'll need 2 points. Many sliding curves can pass through 500= $9.00. What is it for 1000 users or for 1 user?
Last edited by ChemistB; 03-10-2016 at 04:53 PM.
Sandy666 -
Ha! That's so true. And your point is where my trouble comes in. In this scenario the price for 1 user would be quite different than the price for 250 users, but I don't know HOW different it would be. That's what I'm hoping to figure out.
At the end, I want to be able to type in 317 users or 6006 users and get a price per user based on my parameters. At this point all I know is the price for 317 users would be a little more than $9 and the price for 6006 users would be a little less than $2.50.
It is possible I can't actually accomplish this in excel.
ChemistB - Good to know. I'll table trying to figure that out then.
Last edited by ArtlessIbex; 03-10-2016 at 04:51 PM. Reason: clarity
I believe you can figure out almost anything in Excel but not everything is worth the effort. See my edit on post #8
It's immpossible: maybe less maybe more without any definition.
You need define step between ranges, i.e. 1-100, 101-200, 201-300 - all with step 99 (1+99=100, etc)
IMHO no one want FAV with price around but stricte price
If I should pay for sth I want to know it is $9 not maybe $9 or maybe $9.25
You can define e.g. if more users then step is rising also, or decreasing. It's up to you
Last edited by sandy666; 03-10-2016 at 05:02 PM. Reason: typo
Thank you, I can see that you are correct. I need to go back and get clearer parameters.
I believe you are also correct in terms of outlining price to the customer. Nobody wants to be unsure of what they're paying.
Thank you all for your help.
I wish you luck in boxing problem
Okay, with a little trial and error, in order to get a negative slope for each of your 3 tiers, the most logical cost for a single user is $15. With that number, I calculated a slope/intercept for 1-1000, then 1000-10000 and finally 10000 to 25000 based on your criteria. I put those slopes and intercepts into a vlookup table (G7:I9) and use that to determine price in cell B5
Note: If the cost for a single user goes higher than 15, the second tier has a positive slope (more users pay more per user) and if it goes lower than 14, the third tier has a positive slope. So with all other criteria in place, this would be your solution.
Sorry, it double posted.
ChemistB Holy cow, that is beautiful! That is amazing.
I want to send you flowers.
Thank you so much. I can use this AND I actually understand what you did. Thank you again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks