calculating matrix values

[Guest]

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.

Thanks for any help!!!!

 

[Guest]

Yes, but we need more information on how your data is set up on the
worksheet. I'm thinking you must have a sheet/table that lists the various
X's, Y's, and the payout percentages. If that is true you could build a
formula that utilizes some lookup functions.

 

[Guest]

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!!