Formula Challenge II !

[PA HS Teacher]

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
	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!

 

[apolloh]

ClGII.xls
	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		100	565
7	500 to 750	6.75		10000	3825
8	750 to 1000	8.75		700	3300
9	1000 to 2000	10		1500	10825
10	> 2000	-1.5		900	4950
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

 

[malcom]

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).

 

[apolloh]

CLGII2.xls
	A	B	C	D	E	F	G
1	Widgets		Price/Widget		Example:	1	2
2	0	150	5.65		Widgets	Total Price	Total Price
3	150	250	4.65		300	1475	1475
4	250	350	3.25		1200	7825	7825
5	350	475	2.25		2500	15825	15075
6	475	500	1.25		100	565	565
7	500	750	6.75		10000	15825	3825
8	750	1000	8.75		700	3300	3300
9	1000	2000	10		1500	10825	10825
10	2000		-1.5		900	4950	4950
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,))

 

[mortgageman]

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

 

[mortgageman]

Or is it the price for the customer and not the commission?

Gene Klein

 

[PA HS Teacher]

Just because I proposed the question doesn't mean I can't answer right?

Book1
	A	B	C	D	E	F
1	0	Widgets	Price/Widget		Example:
2	150	0 to 150	5.65		Widgets	Total Price
3	250	150 to 250	4.65		300	1475
4	350	250 to 350	3.25		1200	7825
5	475	350 to 475	2.25		2500	15075
6	500	475 to 500	1.25		100	565
7	750	500 to 750	6.75		10000	3825
8	1000	750 to 1000	8.75		700	3300
9	2000	1000 to 2000	10		1500	10825
10		> 2000	-1.5		900	4950
Sheet1

Array Entered in F3:

=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)

 

[PA HS Teacher]

All this, and I'm still trying to understand why >2000 is negative. I mean, why the disencentive to sell more than 2000?

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.

 

[malcom]

challenge2.xls
	A	B	C	D
1	0	5.65	847.5
2	150	4.65	465
3	250	3.25	325
4	350	2.25	281.25
5	475	1.25	31.25
6	500	6.75	1687.5
7	750	8.75	2187.5
8	1000	10	5000
9	2000	-1.5	0
10	0
11		1500	10825
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..

 

[Domenic]

A1 contains the label 'Widgets'

B1 contains the label 'Price/Widget'

A3:B11 contains the following...

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)