Tiered Pricing Model - What am I missing?

[rpfohl]

Units
775

Min Max Price
1 250 $17.00
251 500 $15.00
501 750 $13.00
751 1,000 $11.00

I am trying to determine what I am missing above that is not producing the right result below. The formula I am working with is:

=SUMPRODUCT(--(A1>{0,250,500,750,1000}),A1-{0,250,500,750,1000},{17,-2,-4,-6,-6})

A1 is the # of units. The correct number is $11,525 (for 775 units) but I keep getting $10,875 with this formula so I am not sure what I am doing wrong. In case you are wondering the final "-6" I simply placed in there so the formula would actually produce a number otherwise it was "Value"

I would appreciate some guidance.

[mehmetcik]

Don't Ask Me how it works. It just does.


=LOOKUP(A13,{"",0,1,250,500,750,1000},{"",0,17,17,15,13,11})

[rpfohl]

Thank you, I greatly appreciate it.

[Melvosh]

This will return the result you're looking for:

=SUMPRODUCT(--(A1>{0,250,500,750,1000}),A1-{0,250,500,750,1000},{17,-2,-2,-2,0})

Apparently the third set of numbers needs to be the difference from the previous rate, not from the original rate. I've never messed with this before this post, so I learned something new!

[Melvosh]

Here's a workbook using named ranges that accomplishes the task. I mimicked your data, but doing it this way doesn't require the Min column at all. There may be issues with this using a bigger or more complex data set.

[FlameRetired]

Hello rpfohl. Welcome to the forum.

Here's a reference that may help in the future.

http://www.mcgimpsey.com/excel/variablerate.html