matrix calculations
 

I have an incentive matrix with x and y numeric variables (sales goals and
return on sales) and corresponding payout percentages in the data set. Is
there a way to automatically calculate the payout percentage based on the
known payout rates? There are an infinite amount of x and y possible values.

An example would be the following matrix. What would the payout % be if ROS
(Return on Sales) = 1.5% and Sales Volume = $275,000?

ROS Payout Pecentage
5% 100.0% 125.0% 150.0% 175.0% 200.0%
4% 87.5% 109.4% 131.3% 153.1% 175.0%
3% 75.0% 87.5% 100.0% 125.0% 150.0%
2% 62.5% 78.1% 93.8% 109.4% 125.0%
1% 50.0% 62.5% 75.0% 87.5% 100.0%
$100,000 $200,000 $300,000 $400,000 $500,000 Sales Volume

Thanks for the help!!

matrix calculations
 

Hi Jason,

You need a 2-dimensional interpolation, I think.

Get this: http://www.xlxtrfun.com/XlXtrFun/XlXtrFun.htm

HTH,
Bernd

matrix calculations
 

Jason, you didn't indicate how this table is used. Are intermediate values
interpolated (as Bernd inferred)? Or are these breakpoints, so that when
sales volume reaches 200000 for example, there's an immediate bump in the
payout rate?
If the latter, I'd first flip the rows of the table so that the ROS values
are increasing (required for the match function to work properly). Then you
can use index and match to find the payout rate.
Suppose the table is in A1:F6, with A2:A6 being the ROS breakpoints from 1%
up to 5% and B1:F1 being the sales volume breakpoints from 100k up to 500k.
If the rep's ROS is in B9 and sales volume in C9, then the payout rate is
=IF(OR(B9<$A$2,C9<$B$1),0,INDEX($B$2:$F$6,MATCH(B9 ,$A$2:$A$6),MATCH(C9,$B$1:$F$1))).
That's set up so that you could create the column of ROS, Sales and Payouts
by autofilling that formula.
HTH. --Bruce

"Jason M" wrote:

I have an incentive matrix with x and y numeric variables (sales goals and
return on sales) and corresponding payout percentages in the data set. Is
there a way to automatically calculate the payout percentage based on the
known payout rates? There are an infinite amount of x and y possible values.

An example would be the following matrix. What would the payout % be if ROS
(Return on Sales) = 1.5% and Sales Volume = $275,000?

ROS Payout Pecentage
5% 100.0% 125.0% 150.0% 175.0% 200.0%
4% 87.5% 109.4% 131.3% 153.1% 175.0%
3% 75.0% 87.5% 100.0% 125.0% 150.0%
2% 62.5% 78.1% 93.8% 109.4% 125.0%
1% 50.0% 62.5% 75.0% 87.5% 100.0%
$100,000 $200,000 $300,000 $400,000 $500,000 Sales Volume

Thanks for the help!!