|
Function Vlookup, Match or Index?
Please help me write the function that would match the Month and the Sales to give me the amount of Commission for each combination of Month and Sales Value. Thanks, Patrick Month Sales Commission May-05 - - May-05 $ 297,409 $600 May-05 $ 1,000,000 $833 Jun-05 - - Jun-05 $ 330,515 $800 Jun-05 $ 1,500,000 $900 Jul-05 - - Jul-05 $ 298,390 $500 Jul-05 $ 1,700,000 $1500 Aug-05 - - Aug-05 $ 341,568 $400 Aug-05 $ 1,900,000 $600 Sep-05 - - Sep-05 $ 319,376 $700 Sep-05 $ 2,000,000 $1500 Oct-05 - - Oct-05 $ 379,096 $200 Oct-05 $ 1,000,000 $1800 |
Hello Patrick,
It would help if you explained a little more. Right now we only have what information you are attempting to use to return a value. This does not tell us how your data is structured, which doesn't really allow us to give you a working solution. Try explaining in greater detail how your data is setup, where you are wanting to put this formula and what the desired results should be. -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) "Patrick Young" wrote in message ... Please help me write the function that would match the Month and the Sales to give me the amount of Commission for each combination of Month and Sales Value. Thanks, Patrick Month Sales Commission May-05 - - May-05 $ 297,409 $600 May-05 $ 1,000,000 $833 Jun-05 - - Jun-05 $ 330,515 $800 Jun-05 $ 1,500,000 $900 Jul-05 - - Jul-05 $ 298,390 $500 Jul-05 $ 1,700,000 $1500 Aug-05 - - Aug-05 $ 341,568 $400 Aug-05 $ 1,900,000 $600 Sep-05 - - Sep-05 $ 319,376 $700 Sep-05 $ 2,000,000 $1500 Oct-05 - - Oct-05 $ 379,096 $200 Oct-05 $ 1,000,000 $1800 |
Patrick,
You might also be interested in the SumProduct worksheet function. This one will look at values in one column and pull out only those matching one criteria. Than you can add additional columns to do the same. And than add the last column which is the value column (to add up). The result is the sum for all the concurrent matches. But again - we need details on how your columns are set up... -- steveB Remove "AYN" from email to respond "Patrick Young" wrote in message ... Please help me write the function that would match the Month and the Sales to give me the amount of Commission for each combination of Month and Sales Value. Thanks, Patrick Month Sales Commission May-05 - - May-05 $ 297,409 $600 May-05 $ 1,000,000 $833 Jun-05 - - Jun-05 $ 330,515 $800 Jun-05 $ 1,500,000 $900 Jul-05 - - Jul-05 $ 298,390 $500 Jul-05 $ 1,700,000 $1500 Aug-05 - - Aug-05 $ 341,568 $400 Aug-05 $ 1,900,000 $600 Sep-05 - - Sep-05 $ 319,376 $700 Sep-05 $ 2,000,000 $1500 Oct-05 - - Oct-05 $ 379,096 $200 Oct-05 $ 1,000,000 $1800 |
Hello Zack,
Every month, I have to calculate the commissions to be paid to a list of sales reps, based on the value of their individual sales and the specific commission program (different each month but the same for each sales rep in a given month) for the month. So the variables by sales rep are the month, the amount of the sales, the commission program (same for every sales rep) for the month. I want to copy the required function against each sales rep name to calculate the commission due to him. Thanks for your help, Patrick "Zack Barresse" wrote in message ... Hello Patrick, It would help if you explained a little more. Right now we only have what information you are attempting to use to return a value. This does not tell us how your data is structured, which doesn't really allow us to give you a working solution. Try explaining in greater detail how your data is setup, where you are wanting to put this formula and what the desired results should be. -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) "Patrick Young" wrote in message ... Please help me write the function that would match the Month and the Sales to give me the amount of Commission for each combination of Month and Sales Value. Thanks, Patrick Month Sales Commission May-05 - - May-05 $ 297,409 $600 May-05 $ 1,000,000 $833 Jun-05 - - Jun-05 $ 330,515 $800 Jun-05 $ 1,500,000 $900 Jul-05 - - Jul-05 $ 298,390 $500 Jul-05 $ 1,700,000 $ 1500 Aug-05 - - Aug-05 $ 341,568 $400 Aug-05 $ 1,900,000 $600 Sep-05 - - Sep-05 $ 319,376 $700 Sep-05 $ 2,000,000 $1500 Oct-05 - - Oct-05 $ 379,096 $200 Oct-05 $ 1,000,000 $1800 |
Hello Steve,
Every month, I have to calculate the commissions to be paid to a list of sales reps, based on the value of their individual sales and the specific commission program (different each month but the same for each sales rep in a given month) for the month. So the variables by sales rep are the month, the amount of the sales, the commission program (same for every sales rep) for the month. I want to copy the required function against each sales rep name to calculate the commission due to him. The table below lists the criteria to be used to pay the commissions, and they are ranked in ascending order of sales volume by month. Thanks for your help, Patrick "STEVE BELL" wrote in message news:K2i0f.348$zo6.339@trnddc05... Patrick, You might also be interested in the SumProduct worksheet function. This one will look at values in one column and pull out only those matching one criteria. Than you can add additional columns to do the same. And than add the last column which is the value column (to add up). The result is the sum for all the concurrent matches. But again - we need details on how your columns are set up... -- steveB Remove "AYN" from email to respond "Patrick Young" wrote in message ... Please help me write the function that would match the Month and the Sales to give me the amount of Commission for each combination of Month and Sales Value. Thanks, Patrick Month Sales Commission May-05 - - May-05 $ 297,409 $600 May-05 $ 1,000,000 $833 Jun-05 - - Jun-05 $ 330,515 $800 Jun-05 $ 1,500,000 $900 Jul-05 - - Jul-05 $ 298,390 $500 Jul-05 $ 1,700,000 $ 1500 Aug-05 - - Aug-05 $ 341,568 $400 Aug-05 $ 1,900,000 $600 Sep-05 - - Sep-05 $ 319,376 $700 Sep-05 $ 2,000,000 $1500 Oct-05 - - Oct-05 $ 379,096 $200 Oct-05 $ 1,000,000 $1800 |
Can you tell us where your data is located, giving us an idea of your data
structure? How about a 5-10 row example? -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) "Patrick Young" wrote in message ... Hello Steve, Every month, I have to calculate the commissions to be paid to a list of sales reps, based on the value of their individual sales and the specific commission program (different each month but the same for each sales rep in a given month) for the month. So the variables by sales rep are the month, the amount of the sales, the commission program (same for every sales rep) for the month. I want to copy the required function against each sales rep name to calculate the commission due to him. The table below lists the criteria to be used to pay the commissions, and they are ranked in ascending order of sales volume by month. Thanks for your help, Patrick "STEVE BELL" wrote in message news:K2i0f.348$zo6.339@trnddc05... Patrick, You might also be interested in the SumProduct worksheet function. This one will look at values in one column and pull out only those matching one criteria. Than you can add additional columns to do the same. And than add the last column which is the value column (to add up). The result is the sum for all the concurrent matches. But again - we need details on how your columns are set up... -- steveB Remove "AYN" from email to respond "Patrick Young" wrote in message ... Please help me write the function that would match the Month and the Sales to give me the amount of Commission for each combination of Month and Sales Value. Thanks, Patrick Month Sales Commission May-05 - - May-05 $ 297,409 $600 May-05 $ 1,000,000 $833 Jun-05 - - Jun-05 $ 330,515 $800 Jun-05 $ 1,500,000 $900 Jul-05 - - Jul-05 $ 298,390 $500 Jul-05 $ 1,700,000 $ 1500 Aug-05 - - Aug-05 $ 341,568 $400 Aug-05 $ 1,900,000 $600 Sep-05 - - Sep-05 $ 319,376 $700 Sep-05 $ 2,000,000 $1500 Oct-05 - - Oct-05 $ 379,096 $200 Oct-05 $ 1,000,000 $1800 |
Hi Zack,
The sales by sales rep is located in a Hyperion database that is accessed by Excel as soon as the books are closed at month-end. The Excel spreadsheet is laid out as in the following example:- Month: August 05 Salesrep Sales Commission P $100000 X $300000 Y $200000 Z $150000 I need to calculate the required commission by salesrep from the following table (for August 05 from the above example):- Month Sales Commission May-05 - - May-05 $ 297,409 $600 May-05 $ 1,000,000 $833 Jun-05 - - Jun-05 $ 330,515 $800 Jun-05 $ 1,500,000 $900 Jul-05 - - Jul-05 $ 298,390 $500 Jul-05 $ 1,700,000 $ 1500 Aug-05 - - Aug-05 $ 341,568 $400 Aug-05 $ 1,900,000 $600 Sep-05 - - Sep-05 $ 319,376 $700 Sep-05 $ 2,000,000 $1500 Oct-05 - - Oct-05 $ 379,096 $200 Oct-05 $ 1,000,000 $1800 I hope I have given you enough information this time, Zack. Thanks, Patrick "Zack Barresse" wrote in message ... Can you tell us where your data is located, giving us an idea of your data structure? How about a 5-10 row example? -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) "Patrick Young" wrote in message ... Hello Steve, Every month, I have to calculate the commissions to be paid to a list of sales reps, based on the value of their individual sales and the specific commission program (different each month but the same for each sales rep in a given month) for the month. So the variables by sales rep are the month, the amount of the sales, the commission program (same for every sales rep) for the month. I want to copy the required function against each sales rep name to calculate the commission due to him. The table below lists the criteria to be used to pay the commissions, and they are ranked in ascending order of sales volume by month. Thanks for your help, Patrick "STEVE BELL" wrote in message news:K2i0f.348$zo6.339@trnddc05... Patrick, You might also be interested in the SumProduct worksheet function. This one will look at values in one column and pull out only those matching one criteria. Than you can add additional columns to do the same. And than add the last column which is the value column (to add up). The result is the sum for all the concurrent matches. But again - we need details on how your columns are set up... -- steveB Remove "AYN" from email to respond "Patrick Young" wrote in message ... Please help me write the function that would match the Month and the Sales to give me the amount of Commission for each combination of Month and Sales Value. Thanks, Patrick Month Sales Commission May-05 - - May-05 $ 297,409 $600 May-05 $ 1,000,000 $833 Jun-05 - - Jun-05 $ 330,515 $800 Jun-05 $ 1,500,000 $900 Jul-05 - - Jul-05 $ 298,390 $500 Jul-05 $ 1,700,000 $ 1500 Aug-05 - - Aug-05 $ 341,568 $400 Aug-05 $ 1,900,000 $600 Sep-05 - - Sep-05 $ 319,376 $700 Sep-05 $ 2,000,000 $1500 Oct-05 - - Oct-05 $ 379,096 $200 Oct-05 $ 1,000,000 $1800 |
| All times are GMT +1. The time now is 12:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com