View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jason M
 
Posts: n/a
Default 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!!