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.
- 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)- is there a way to post a screen shot of my spreadsheet? Or to reply to you directly?
- 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 %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.
