Hi!
I need help making an Excel tiered pricing calculator/formula for the following pricing model:
# of Users Price per User
1-5 $59.88
6-15 $49.99
16-30 $47.99
31-50 $44.99
51-100 $39.99
101-250 $34.99
251-500 $29.99
501-1000 $24.99
1001+ $19.99
For example, if there were 15 users, it would charge $59.88 for the first 5 users plus $49.99 for the next 10 users.
Thanks in advance!
The website won't let me post my formula for some reason , so have a look at the attached.
I don't agree with Kersplash's answer for 15 items.
I've used his / her spreadsheet though to show what I cam up with. A slightly shorter formula and its giving I think the right answer for the few item numbers I've checked it against
Happy with my advice? Click on the * reputation button below
Actually have revised formula and spreadsheet. Apologies to Kersplash - I get the same numbers now (but I do have a shorted formula if you wish to use it)
Entered as an array formula
Please Login or Register to view this content.
And by the way - Welcome to the forum
kersplash I looked at your formula. Sucurri is hyper sensitive (and fickle) about characters such "<" and ">". Try putting a leading and trailing space around them.Originally Posted by kersplash
Dave
Another way
=SUMPRODUCT((D2>A2:A10)*(D2-A2:A10)*(B2:B10-B1:B9))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks