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]

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

E3=IF(D3<=$A$3,D3*$B$2,SUM(MMULT(SUMIF(OFFSET($A$2,ROW(INDIRECT("1:"&MATCH(D3,$A:$A)-2))-{0,1},,,),"<>")*{1,-1}*OFFSET($B$2,,,MATCH(D3,$A:$A)-2),{1;1}))+(D3-OFFSET($A$2,MATCH(D3,$A:$A)-2,))*OFFSET($B$2,MATCH(D3,$A:$A)-2,))


Using this formula it is allowed to expand the tiered commision and tiered pricing to the whole column A and column B.

 

[Domenic]

Using my same layout, this formula will also allow the data to expand...

=SUMPRODUCT(--(D3>$A$3:INDEX(A:A,MATCH(9.99999999999999E+307,A:A))),D3-$A$3:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)),$B$3:INDEX(B:B,MATCH(9.99999999999999E+307,A:A))-$B$2:INDEX(B:B,MATCH(9.99999999999999E+307,A:A)-1))

 

[apolloh]

Using my same layout, this formula will also allow the data to expand...

=SUMPRODUCT(--(D3>$A$3:INDEX(A:A,MATCH(9.99999999999999E+307,A:A))),D3-$A$3:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)),$B$3:INDEX(B:B,MATCH(9.99999999999999E+307,A:A))-$B$2:INDEX(B:B,MATCH(9.99999999999999E+307,A:A)-1))

A question:
Which one is faster?

It seem that all the data is put into calcuating in your formula,although the value of D3 is only 300,in fact,it is enough for caluating to get there rows ahead of the data.

 

[Domenic]

A question: Which one is faster?

I'm not sure, but I'm guessing that my formula is faster. It has fewer function calls. Also, your formula uses INDIRECT and OFFSET which as you know are volitile.

 

[apolloh]

INDEX is a volitile function too。

It is still faster to calculate with a seval rows by calling a volitile function than a whole lot of rows.

You can test it by filling the data down into the whole columns.

 

[Domenic]

INDEX is volitile function also?

Actually, if I'm not mistaken, INDEX is volitile only on some versions of Excel. I remember reading something about that somewhere but I can't seem to find that reference at the moment.

It is still faster to calculate with a seval rows by calling a volitile function than a whole lot of rows.

Since the range is a dynamic one, I don't think the calculations are carried out for the entire column. Only for the dynamic range. Or am I wrong?

 

[apolloh]

MATCH(9.99999999999999E+307,A:A)

If last row is 65536,this part returns a number 65536.

But in my formula, if D3 is 300, the formual calculates with 3 rows although the last row is 65536

 

[Domenic]

MATCH(9.99999999999999E+307,A:A)

If last row is 65536,this part returns a number 65536.

But in my formula, if D3 is 300, the formual calculates with 3 rows although the last row is 65536

Ah yes, I see what you mean. Thanks Apolloh!

By the way, I found the reference regarding volitlity. It seems that INDEX became non-volitile in Excel 97...

http://www.decisionmodels.com/calcsecretsi.htm

 

[apolloh]

Thank you also for INDEX.

 

[apolloh]

A litte shorter

=IF(D3<=$A$3,D3*$B$2,SUM(MMULT(SUMIF(OFFSET($A$2,ROW(INDIRECT("1:"&MATCH(D3,$A:$A)-2))-{0,1},,,),"<>")*OFFSET($B$2,,,MATCH(D3,$A:$A)-2),{1;-1}))+(D3-OFFSET($A$2,MATCH(D3,$A:$A)-2,))*OFFSET($B$2,MATCH(D3,$A:$A)-2,))