PA HS Teacher
Well-known Member
- Joined
- Jul 17, 2004
- Messages
- 2,838
Formula Challenge I:
http://www.mrexcel.com/board2/viewtopic.php?t=156081&postdays=0&postorder=asc&start=0
I've run into a number of tiered commision and tiered pricing questions on this board. I don't have them bookmarked, but there have been some creative solutions. I propose the following challenge.
You must determine the price of X widgets according to a table similar to the following:
I've presented three examples above, but the total price is determined as the first 150 widgets are 5.65 each, the next 100 are 4.65 each etc.
Conditions:
1. Your formula must return the correct price for a given number of Widgets.
2. Your formula must be a single cell formula. (e.g. no helper columns)
3. You must use only native Excel formula, no VBA, no analysis tool pack etc. (though not eligble for the contest, VBA solutions are always welcome for discussion.)
4. You can reconfigure the given table to meet your needs, but your formula must be set up in a way that you do not need to know the number of tiers, or value for each tier ahead of time.
5. The values in column B of the table above can be positive or negative, and to not follow an intential pattern.
6. The size of the tiers varies, and is not known ahead of time.
There may be more than one type of winner, but I propose the following 4 categories.
A. Most Transparent
B. Most Flexible
C. Most Efficient
D. Shortest (least number of characters)
E. Ugliest (a.k.a. the biggest cludge)
Entries will be accepted through 1 week from now, or when the dialogue effecdtively stops, whichever is longer.
Discussion is encouraged.
Judging will be carried out at the end, by all readers of this post, willing to put their two cents in. (give their opinion)
If anyone feels I have missed something, feel free to propose rule changes.
Have Fun!
http://www.mrexcel.com/board2/viewtopic.php?t=156081&postdays=0&postorder=asc&start=0
I've run into a number of tiered commision and tiered pricing questions on this board. I don't have them bookmarked, but there have been some creative solutions. I propose the following challenge.
You must determine the price of X widgets according to a table similar to the following:
Book1 | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Widgets | Price/Widget | Example: | ||||
2 | 0 to 150 | 5.65 | Widgets | Total Price | |||
3 | 150 to 250 | 4.65 | 300 | 1475 | |||
4 | 250 to 350 | 3.25 | 1200 | 7825 | |||
5 | 350 to 475 | 2.25 | 2500 | 15075 | |||
6 | 475 to 500 | 1.25 | |||||
7 | 500 to 750 | 6.75 | |||||
8 | 750 to 1000 | 8.75 | |||||
9 | 1000 to 2000 | 10 | |||||
10 | > 2000 | -1.5 | |||||
Sheet1 |
I've presented three examples above, but the total price is determined as the first 150 widgets are 5.65 each, the next 100 are 4.65 each etc.
Conditions:
1. Your formula must return the correct price for a given number of Widgets.
2. Your formula must be a single cell formula. (e.g. no helper columns)
3. You must use only native Excel formula, no VBA, no analysis tool pack etc. (though not eligble for the contest, VBA solutions are always welcome for discussion.)
4. You can reconfigure the given table to meet your needs, but your formula must be set up in a way that you do not need to know the number of tiers, or value for each tier ahead of time.
5. The values in column B of the table above can be positive or negative, and to not follow an intential pattern.
6. The size of the tiers varies, and is not known ahead of time.
There may be more than one type of winner, but I propose the following 4 categories.
A. Most Transparent
B. Most Flexible
C. Most Efficient
D. Shortest (least number of characters)
E. Ugliest (a.k.a. the biggest cludge)
Entries will be accepted through 1 week from now, or when the dialogue effecdtively stops, whichever is longer.
Discussion is encouraged.
Judging will be carried out at the end, by all readers of this post, willing to put their two cents in. (give their opinion)
If anyone feels I have missed something, feel free to propose rule changes.
Have Fun!