Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
commission
i have two questions that are connected . i want to produce on 4 different
spreadsheets data from one spreadsheet to work out commissions. A consultants bonus is paid and worked out quarterly.The more fees earnt in a quarter the higher the bonus. At quarter end the calculation starts again I want one spreadsheet to capture every sale. So columns one will be "sales date" ,column 2 "revenue", column 3 commission rate, column 4 commission. Then the other spreadsheets will be quarter periods.So quarter one is Jan-March, the next spreadsheet is April-June and so i want to write something in spreadsheet 2 so that it puls from the main spreadsheets all rows of information that have a sales date 1st Jan-31st March and then copy the columns.If the date is 1st April ,it sends this information to Spreadsheet 3 and so on.Then , in the quarter spreadsheets i can write a if statement to calculate.I am struggling to write something that copies the data to the correct spreadsheets i hope that makes sense -- nick |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
commission
nick,
There are much better ways to do that. 1 Use one sheet, with a fifth column "Quarter". Enter the correct quarter for every entry. You should also enter which consultant you have - that way, you won't need a separate workbook for each consultant. Then, when you want to see a specific quarter, apply filters to your data to show just that quarter, for that consultants, and use SUBTOTAL functions to total just the visible cells. 2 Or use a pivot table based on the data and group the data by quarter and by consultant - Excel can figure out the quarter based on the date, so you don't even need to use another column. 3 Use SUMPRODUCT functions to figure out the values - a little more advanced, so we're not going to confuse you.... HTH, Bernie MS Excel MVP "nick thompson" wrote in message ... i have two questions that are connected . i want to produce on 4 different spreadsheets data from one spreadsheet to work out commissions. A consultants bonus is paid and worked out quarterly.The more fees earnt in a quarter the higher the bonus. At quarter end the calculation starts again I want one spreadsheet to capture every sale. So columns one will be "sales date" ,column 2 "revenue", column 3 commission rate, column 4 commission. Then the other spreadsheets will be quarter periods.So quarter one is Jan-March, the next spreadsheet is April-June and so i want to write something in spreadsheet 2 so that it puls from the main spreadsheets all rows of information that have a sales date 1st Jan-31st March and then copy the columns.If the date is 1st April ,it sends this information to Spreadsheet 3 and so on.Then , in the quarter spreadsheets i can write a if statement to calculate.I am struggling to write something that copies the data to the correct spreadsheets i hope that makes sense -- nick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Commission Percentage | Excel Discussion (Misc queries) | |||
Sales Commission | Excel Programming | |||
Sales V commission | Excel Worksheet Functions | |||
Commission Calculation | Excel Worksheet Functions | |||
calculate commission $ based on total sold and commission percent | Excel Programming |