Tiered pricing / =sumproduct help requested

Hello,

I've read through multiple threads on this and can't seem to make the =sumproduct formula work for a tiered pricing problem I'm working on. The data looks like this:

Volume Breakpoint	Price / Rate
2000	$0
15000	$2.00
30000	$1.50
45000	$1.25
60000	$1.00

Also, I've used a defined name for column A2:A6, 'rB' (rate breakpoint) and for column B2:B6, 'rRate' (rate). The data I'm trying to compare (to set a rate for, essentially), looks like: 2001, 15005, 30002, 45001, 60002). The data occupies cells G3:K3.

The formula I've been using looks like this:

Please Login or Register  to view this content.

The first result is correct, but every subsequent result is way off. Any suggestions on how I could make this work?

Also, once I have the rate established, I still need to multiply the unit cost x the rate. Perhaps this could be done in the same sumproduct formula?

Thank you very much in advance!

For a tiered calc a basic construct would be:

=SUMPRODUCT(--(value>={min threshold array}),value-{min threshold array},{incremental/marginal values array})

so, using your example:

=SUMPRODUCT(--(G3>=rB),G3-rB,{0;2;-0.5;-0.25;-0.25})

note: if you change your rate range (rRate) to reflect the incremental movement between the tiers you can use this to replace the inline array above - i.e. rRate versus {0;...}

Thanks, AliGW, and XLent. I tried them both and have so far found the simplicity of the VLOOKUP to be more effective. However, I'm getting #N/A errors on zero values when evaluated. Do you have any suggestions for a reformulation of the VLOOKUP for handling zero values?

Thanks very much!

Thank you both very much for your help. I was able to make this work with a similar =IFERROR formula.

A quick question regarding the =SUMPRODUCT function as outlined above from Xlent. If this formula were repeated / copied throughout multiple cells, is there a way to ensure values in the array could be simultaneously updated via cell references? Thus, would I be able to update a pricing table without updating every value in every array? Or are they hardcoded? That was the piece I didn't understand.

Thanks!

This is such an important concept and distinction, XLent. Thank you very for the insight.

Hello All!

I need help figuring out a pricing tier for a compensation plan on EXCEL 2016.

Pricing is as follows:

$1,000,000 x .0027= $2700
$2,000,000 x .0035= $3500
$3,000,000 x .0045= $4500
$4,000,000 x .0055= $5500
>$5,000,000 x .007= $7000

I have a sum of total dollar amounts but am at a loss for how to calculate for the tier dollar amounts per million.

Thank you!