Formula Challenge II !

PA HS Teacher

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:
Book1
ABCDE
1WidgetsPrice/WidgetExample:
20 to 1505.65WidgetsTotal Price
3150 to 2504.653001475
4250 to 3503.2512007825
5350 to 4752.25250015075
6475 to 5001.25
7500 to 7506.75
8750 to 10008.75
91000 to 200010
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!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
ClGII.xls
ABCDE
1WidgetsPrice/WidgetExample:
20 to 1505.65WidgetsTotal Price
3150 to 2504.653001475
4250 to 3503.2512007825
5350 to 4752.25250015075
6475 to 5001.25100565
7500 to 7506.75100003825
8750 to 10008.757003300
91000 to 200010150010825
10> 2000-1.59004950
Sheet1


E3=SUM(((D3-RIGHT($A$2:$A$9,LEN($A$2:$A$9)-FIND("o",$A$2:$A$9)-1))>0)*(RIGHT($A$2:$A$9,LEN($A$2:$A$9)-FIND("o",$A$2:$A$9)-1)-LEFT($A$2:$A$9,FIND("t",$A$2:$A$9)-2))*$B$2:$B$9)+SUM(FREQUENCY(D3,RIGHT($A$2:$A$9,LEN($A$2:$A$9)-FIND("o",$A$2:$A$9)-1)*1)*(D3-IF(ROW($A$2:$A$10)-10,LEFT($A$2:$A$9,FIND("t",$A$2:$A$9)-2),MID($A$10,3,15)))*$B$2:$B$10)

Confirmed with Ctrl+shift+enter
 
Upvote 0
apolloh, uve got a very long formula.. how about considering Condition 4..
"You may reconfigure the given table to meet your needs".. i believe it will make ur formula much shorter.. then, maybe u have to consider condition 6 also..

i believe the table must be reconfigured.. it should have been 0 to 150 then 151 to 250 and so on... but to make formula shorter, its more practical to have 0 and 150 in 2 cells, (0) and (150).. rather than a single cell (0 to 150).
 
Upvote 0
CLGII2.xls
ABCDEFG
1WidgetsPrice/WidgetExample:12
201505.65WidgetsTotal PriceTotal Price
31502504.6530014751475
42503503.25120078257825
53504752.2525001582515075
64755001.25100565565
75007506.7510000158253825
875010008.7570033003300
9100020001015001082510825
102000-1.590049504950
Sheet1



F3=SUMPRODUCT((E3-$B$2:$B$10>0)*($B$2:$B$10<>"")*$C$2:$C$10*($B$2:$B$10-$A$2:$A$10))+MAX(FREQUENCY(E3,$B$2:$B$9)*$C$2:$C$10*(E3-$A$2:$A$10))

or

G3=IF(E3<$B$2,E3*$C$2,SUM(MMULT(SUMIF(OFFSET($A$2,ROW(INDIRECT("1:"&MATCH(E3,$B:$B)-1))-1,{0,1},,),"<>")*{-1,1}*OFFSET($C$2,,,MATCH(E3,$B:$B)-1),{1;1}))+(E3-OFFSET($A$2,MATCH(E3,$B:$B)-1,))*OFFSET($C$2,MATCH(E3,$B:$B)-1,))
 
Upvote 0
All this, and I'm still trying to understand why >2000 is negative. I mean, why the disencentive to sell more than 2000?

Gene Klein
 
Upvote 0
Just because I proposed the question doesn't mean I can't answer right?
Book1
ABCDEF
10WidgetsPrice/WidgetExample:
21500 to 1505.65WidgetsTotal Price
3250150 to 2504.653001475
4350250 to 3503.2512007825
5475350 to 4752.25250015075
6500475 to 5001.25100565
7750500 to 7506.75100003825
81000750 to 10008.757003300
920001000 to 200010150010825
10> 2000-1.59004950
Sheet1


Array Entered in F3:
Code: 
=SUMPRODUCT(
(E3>IF(A$2:A$100,A$2:A$10,9.9E+307))*(A$2:A$100-A$1:A$99)*C$2:C$100+
(E3>A$1:A$99)*((E3<=A$2:A$100)+(A$2:A$100=0))*(E3-A$1:A$99)*C$2:C$100)
 
Upvote 0
All this, and I'm still trying to understand why >2000 is negative. I mean, why the disencentive to sell more than 2000?
Click to expand...

I just added that to make the solution more general. However, I will make up an abusurd reason. Orders of more than 2000 widgets allow you to receive a government subsidy of 3.00 per widget, however, to be eligible for the subsidy, you are not allowed to make more than .50 per Widget. It costs you 1.00 per Widget. This means you must pay the customer 1.50 per widget over 2000.

[edit] Yes, the number returned is the price for a customer to order X Widgets.
 
Upvote 0
challenge2.xls
ABCD
105.65847.5
21504.65465
32503.25325
43502.25281.25
54751.2531.25
65006.751687.5
77508.752187.5
81000105000
92000-1.50
100
11150010825
Sheet3


C1 = =B1*MAX(MIN($B$11,A2)-A1,0)
.
.
C5 = =B5*MAX(MIN($B$11,A6)-A5,0)
.
.
C9 = =B9*MAX(MIN($B$11,A10)-A9,0)

then

C11 = =SUM(C1:C9)

any idea how to turn this to array formula? summing up C1 to C9..
 
Upvote 0
A1 contains the label 'Widgets'

B1 contains the label 'Price/Widget'

A3:B11 contains the following...

Code: 
0	5.65
150	4.65
250	3.25
350	2.25
475	1.25
500	6.75
750	8.75
1000	10
2000	-1.5

E3, copied down:

=SUMPRODUCT(--($A$3:$A$100<>""),--(D3>$A$3:$A$100),D3-$A$3:$A$100,$B$3:$B$100-$B$2:$B$99)
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Performance issue using a combination of dropdowns & lookups
Replies
9
Views
616
Akuini
Akuini
P
  • Question Question
What is the formula?
Replies
1
Views
584
Joe4
Joe4
C
  • Question Question
Several conditional formatting solutions needed (Excel 2003)
Replies
7
Views
726
xenou
xenou
I
  • Question Question
Which functions to combine/insert html + data?
Replies
0
Views
1K
IGinc
I
P
  • Question Question
Index Return from Multiple Columns to Single Column
Replies
4
Views
797
Domenic
D

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top