Forum Discussion
Commission % drop down
HI all. So, this is why my brain is hurting. Please help. I have a basic spreadsheet that I track my orders, and commission rates. Depending on who the customer is I receive either 2% or 6% ...
either add another column for that drop down and then column K would have the calculated commission or use a lookup table and use XLOOKUP or VLOOKUP. So if you make J7 the drop down then K7 would be =I7*J7
If you have a lookup table it would be something like =I7*XLOOKUP(A7,Table1[name],Table1[commission],0)
If you have a lookup table it would be something like =I7*XLOOKUP(A7,Table1[name],Table1[commission],0)
column A: Customer Name
Column B : Order date
Column C: Ship date
Column D Invoice Date
Column E: Invoice number
Column F: Order total
Column G: Freight charge
Column H: Freight Surcharge
Column I: Net order total (Column F -(G+H))
Column J: Commission amount (column I * 2% or 6%)
Or, can I create a list of customers that are at 2% and a list that are at 6%? Then when I enter the name is column A, Column J will auto populate with the correct %
Column B : Order date
Column C: Ship date
Column D Invoice Date
Column E: Invoice number
Column F: Order total
Column G: Freight charge
Column H: Freight Surcharge
Column I: Net order total (Column F -(G+H))
Column J: Commission amount (column I * 2% or 6%)
Or, can I create a list of customers that are at 2% and a list that are at 6%? Then when I enter the name is column A, Column J will auto populate with the correct %
- If the list of customers is 'smaller' and this list is 'bigger' then a separate list of customers and their % makes sense. If not, then just add the % in column J and then make K the calculated amount. I don't recommend you make 2 lists (1 list of customers with 2% and 1 list of customers with 6%) . It sounds 'convenient' and easy but the Lookup is much easier with 1 list as noted above and when you end up adding a customer with 4% or 8% or 1% then you have more lists. You can easily 'look up' the 2% or 6% by filtering that 1 list or search for a name in that 1 list to find which % they are.
Yes, create a list.
For example, unique customers in P2:P50, and the corresponding commission percentage in Q2:Q50.
The formula in J2 would be
=XLOOKUP(A2, $P$2:$P$50, I2*$Q$2:$Q$50, "")
or if you have an older version of Excel
=IFERROR(I2*VLOOKUP(A2, $P$2:$Q$50, 2, FALSE), "")
This can be filled down.
