Formula Challenge II !

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!
 
CLGII2.xls
ABCDE
1WidgetsPrice/WidgetExample:2
205.65WidgetsTotal Price
31504.653001475
42503.2512007825
53502.25250015075
64751.25100565
75006.75100003825
87508.757003300
9100010150010825
102000-1.59004950
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.
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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))
 
Upvote 0
Domenic said:
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.
 
Upvote 0
apolloh said:
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.
 
Upvote 0
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.
 
Upvote 0
apolloh said:
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?
 
Upvote 0
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
 
Upvote 0
apolloh said:
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
 
Upvote 0
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,))
 
Upvote 0

Forum statistics

Threads
1,223,247
Messages
6,171,007
Members
452,374
Latest member
keccles

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