Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matrix calculations
Hi Jason,
You need a 2-dimensional interpolation, I think. Get this: http://www.xlxtrfun.com/XlXtrFun/XlXtrFun.htm HTH, Bernd |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matrix calculation | Excel Discussion (Misc queries) | |||
How to identify entries in a matrix also present in another list | Excel Worksheet Functions | |||
Limits of MINV matrix function | Excel Worksheet Functions | |||
Calculations crossing multiple sheets | Excel Discussion (Misc queries) | |||
Recreate the upper part of a symetric data matrix 75x75 | Excel Discussion (Misc queries) |