Calculate Commission based on Drop down Selection

[gkstan]

I'm trying to create a formula that will calculate commission based on text drop down menu. There are multiple selections and two commission rates. I realize I could create another column but I'm trying to keep everything as compact as possible.

For example.

Column C called item type contains my drop down menu.
The items in the menu are stored in AB8-AB11
Sold column is K
and Commission column is L.

The selections in the drop down menu are Car, Motorcycle, Boat with commission percentage being 20% for car, 15% for motorcycle and 10% for boat.

I'm trying to do this

If item type = "Car" than commission = sold premium * 20%
If item type = "motorcycle" than commission = sold premium * 15%
if item type = boat than commission = sold premium*10%

I have gotten it to work for one selection, say car by entering =if(c8="car",k8*20%), just can't figure out how to add multiple choices.

Thank you for your help

 

[Nebu]

Hi:

Please find the attached. If you change the drop downs in the yellow colored cell, the Comm will be auto populated.

Thanks

 

[Hui]

GKStan

Fistly, Welcome to the Chandoo.org Forums

If the list is short say 3 or 4 items you can use and If Statement like you have
=if(c8="car",20%,if(c8="motorcycle",15%,if(c8="boat",10%,0%)))*K8

If the list is substantially longer, you would be better using a lookup table where you have a list of Vehicles in Say Column A and Commission in Column B

=K8*Vlookup(C8,A1:B20,2)
adjust for where your table is

 

[halfmanhalfspreadsheet]

Hi,

You need to use a vlookup formula. A vlookup looks for a value in the leftmost column of a table and then returns a value from that same row (how far along that row is determined by you).

The vlookup formula consists of 4 parts:

1. SearchFor this is the value that is being searched for. In our dictionary example it would be the word “elucidate”.
2. WhereToSearch this is the range in which to search and in which the answer lives. In our example, it would be columns A to C.
3. WhichColumn this is the column in the WhereToSearch range which has the answer in it. In our dictionary example, it would be the third column.
4. NearOrExact you decide whether the vlookup should search for a close value or an exact value (in our dictionary example we would set this to false as we do want it to search for the exact word “elucidate”).

Structure of the vlookup formula:
=vlookup ( SearchFor , WhereToSearch , WhichColumn , NearOrExact )

So you'd make a table with two columns, first being car, motorbike etc. and the second being their corresponding percentages. You then refer to this table from your existing data (the dropdown box).

(Source: www.excelvlookuphelp.com)

I'm trying to create a formula that will calculate commission based on text drop down menu. There are multiple selections and two commission rates. I realize I could create another column but I'm trying to keep everything as compact as possible.

For example.

Column C called item type contains my drop down menu.
The items in the menu are stored in AB8-AB11
Sold column is K
and Commission column is L.

The selections in the drop down menu are Car, Motorcycle, Boat with commission percentage being 20% for car, 15% for motorcycle and 10% for boat.

I'm trying to do this

If item type = "Car" than commission = sold premium * 20%
If item type = "motorcycle" than commission = sold premium * 15%
if item type = boat than commission = sold premium*10%

I have gotten it to work for one selection, say car by entering =if(c8="car",k8*20%), just can't figure out how to add multiple choices.

Thank you for your help

 

[gkstan]

Thank you so much gentlemen. That was of great help! Understand the vlookup commad now.