Excel rookie need help with formula for data entry/ simple commission rate calculation

I have a spreadsheet where I have to enter in commission rates that vary based on the type of policy it is. So, in column A is a UID, column B is the policyholder's name, column C would be the premium date, Column D would be the type of insurance plan, Column E the premium amount in dollars, column F- the commission on the premium in dollars, Column G the name of the agent/agency that gets paid the commission.

Is there a way to enter a formula in the column the C- the commission on the premium, that calculates the rate of commission which varies depending on the type of policy it is, which is listed in column D. There usually 5-7 type of plans per compensation statement that I have to enter into the spreadsheet.

I was thinking that there was some formula that could calculate the commission based on the type of plan listed in the plan type column. Any help is greatly appreciated.

One way of accomplishing your goal would be to make a table with policy types and the percentage commission for each type, for example the table could be put in columns J and K. Then your formula would multiply the premium in column E by the lookup value of the percentage assigned to the policy type. The formula might look like this:

Please Login or Register  to view this content.

which I have applied in the example worksheet attached.

Policy Commissions.xlsx

I suggest that in the future you attach a small desensitized worksheet with some data and manually calculated solutions (commissions in this case), to make it easier for people on the board to work on writing you a formula.

Hope this helps.

hanks! You are awesome! I didn't realize I could attach spreadsheets to posts. I will do this in the future. I will be posting again soon. One last thing, when you say desensitize a worksheet I assume you mean replace the confidential information with random figures for the sake of learning the necessary Excel function.

Solved. Thank you again FDibbins. This board is my new favorite board.