Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up - i think
i am using a workbook to calculate commission payments
one worksheet contains info on the sale made: Name of customer/date/product/rental/term of rental/total value etc The product is a drop down from a list in another sheet Also on the other sheet is the commission I rate for calcualting the value of the sale What i want to do is write a formula that allows for the sale data to be entered, then for the commission to be calcluated based on the results of selelcting the drop down. For example: Column d = product from drop down Column g - total order value column i - Total order value *(the rate of commission of the product selected at Column d - the value is in the same worksheet as teh product list - one column to the right of the product name) Can anyone help me TIA |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up - i think
If your product list is sorted, you can use VLOOKUP to find the commission
rate, eg in row 2 =VLOOKUP(D2,Products,2,FALSE)*I2 If your products list range name does not include the rate column, then select the product range as well as the rate cells, click on Insert|Name|Define, and give it a descriptive name, eg Products as in the sample, or Prodcomm. "Alan Davies" wrote: i am using a workbook to calculate commission payments one worksheet contains info on the sale made: Name of customer/date/product/rental/term of rental/total value etc The product is a drop down from a list in another sheet Also on the other sheet is the commission I rate for calcualting the value of the sale What i want to do is write a formula that allows for the sale data to be entered, then for the commission to be calcluated based on the results of selelcting the drop down. For example: Column d = product from drop down Column g - total order value column i - Total order value *(the rate of commission of the product selected at Column d - the value is in the same worksheet as teh product list - one column to the right of the product name) Can anyone help me TIA |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up - i think
Sorry, to keep it neat, and not display anything before entries have been
made, use the following instead: =IF(I2="","",VLOOKUP(D2,Products,2,FALSE)*I2) "Alan Davies" wrote: i am using a workbook to calculate commission payments one worksheet contains info on the sale made: Name of customer/date/product/rental/term of rental/total value etc The product is a drop down from a list in another sheet Also on the other sheet is the commission I rate for calcualting the value of the sale What i want to do is write a formula that allows for the sale data to be entered, then for the commission to be calcluated based on the results of selelcting the drop down. For example: Column d = product from drop down Column g - total order value column i - Total order value *(the rate of commission of the product selected at Column d - the value is in the same worksheet as teh product list - one column to the right of the product name) Can anyone help me TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|