Stumped

[dhaderle]

Math problem for you all that I'm trying to solve without a bunch of IF statements or lookups.

I have an input cell for widgets. I price 400 widgets at $32.00 each. For every additional widget the price will be one penny less. So, 401 widgets entered in will yield a price of $31.99, 402 widgets equal $31.98, etc, etc....
At 500 widgets, the price decrease changes to two pennies less for every incremental widget. At 600 widgets the price then is fixed at $29.00 At 400 or less the price is $32.00/each.

Is there a formula/function able to solve this?

Thanks!

 

[Ronald Moore]

Thanks Ronald! Though, I don't quite understand it the formula.

Sorry for the delay in responding. I seem to be missing some of my E-mail notifications.

I don't know of any good way to explain the formula other than by having you examine the behavior of the individual terms. In the sheet below I've shown the results for some of the terms around the key breakpoints in your pricing scheme (400, 500, and 600 units). The headers in columns B, C, D, E are the formulas in row 2.

Book1
	A	B	C	D	E
1	# widgets	= MAX( A2/100, 4)	= MAX( A2 /100 , 5)	= 41 - B2 - C2	= MAX( 29 , D2)
2	1	4.00	5.00	32.00	32.00
3	399	4.00	5.00	32.00	32.00
4	400	4.00	5.00	32.00	32.00
5	401	4.01	5.00	31.99	31.99
6	402	4.02	5.00	31.98	31.98
7	499	4.99	5.00	31.01	31.01
8	500	5.00	5.00	31.00	31.00
9	501	5.01	5.01	30.98	30.98
10	502	5.02	5.02	30.96	30.96
11	599	5.99	5.99	29.02	29.02
12	600	6.00	6.00	29.00	29.00
13	601	6.01	6.01	28.98	29.00
Sheet2

For up to 400 widgets, the column B and column C results are constant at 4.00 and 5.00. These results combine to give a column D result of 32.00, as you stipulated.

Observe the column B result when the number of widgets starts exceeding 400. It increases by .01 for each additional widget. The column C result is still fixed at 5.00 at this point. Column B contributes negatively to column D, so the net effect on column D is that it begins decreasing by .01 for each additional widget.

When the number of widgets starts exceeding 500, the column C result begins increasing by .01 for each additional widget (and column B is still increasing by .01 per widget). So the net effect on column D is that it begins decreasing by .02 for each additional widget.

How long does this go on? Well, it happens that your pricing policy of 29.00 each for 600 or more units was consistently chosen, in that it agrees with the column D result at 600 units. In other words, there was no sudden discontinuity in your pricing at 600 units. So it is sufficient to limit the column D result to 29 or greater, which is just what column E does. If there had been some discontinuity in pricing at 600 units, some further logic would have been required.

 

[dhaderle]

Now I understand. This is brilliant. Thanks!