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% commission. Is there a way to have a cell specific drop down that has 2 different formulas for calculating my commission amount? For example Cell I7 is the calculated total commissionable $ and Cell J7 is the calculation field for commission bases on who the customer is. Can I have a drop down that says "2%, 6%" and depending on which I pick it will calculate the commission on Cell I7?
3+ hours and my brain hurts trying to figure this out.
5 Replies
- 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)- 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 %- 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.
- is there a way to post a screen shot of my spreadsheet? Or to reply to you directly?
