Question description:
This user has given permission to use the problem statement for this
blog.
i have 4 different payment commission options for my sales team. I want to use drop down for them to select which type of cell it is. depending on what they select I want the formula to calculate their commission based on where they finished in the month. My main problem is i can't get my calculation in the vlookup to correspond with which row and cell.
Here is an example of the formula i am working with
=IF(revenueF25<40000,comm!D7*0.22,IF(AND(revenue!F25>=40000,revenue!F25<=70000),comm!D7*0.25,comm!D7*0.28)*10)
Solved by S. Y. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
08/04/2018 - 09:36
Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern.
User
08/04/2018 - 09:36
hey there
Excelchat Expert
08/04/2018 - 09:37
I understand you need help in vlookup function. Do you have a sample of the file so I can help you with your concern?
User
08/04/2018 - 09:37
not really but i can show you on the spreadsheet of what i have in mind
Excelchat Expert
08/04/2018 - 09:38
Sure, please show it on the document preview. Thanks
User
08/04/2018 - 09:41
so basically I want a drop down under type of sale and depending on what they choose either snb, s&L or com the results comes out under the commission section
Excelchat Expert
08/04/2018 - 09:41
ok I see
User
08/04/2018 - 09:42
I have the if forumula figured out because their commission goes up depending on where they are in the month but the % changed depending on what they sale
Excelchat Expert
08/04/2018 - 09:42
You have different calculations depending on type of cell correct
Excelchat Expert
08/04/2018 - 09:42
can you create a table on the formula for each type of cell? what are the differences between the 4 calculations?
User
08/04/2018 - 09:43
just the percents they get paid
Excelchat Expert
08/04/2018 - 09:43
also, what is the fourth sale
Excelchat Expert
08/04/2018 - 09:43
yep please let me know what % each sale has
Excelchat Expert
08/04/2018 - 09:43
You may add it in COlumn H
User
08/04/2018 - 09:44
so complicated
Excelchat Expert
08/04/2018 - 09:45
also, I can see that Revenue is also included on your sample formula, please also give the formula for revenue
Excelchat Expert
08/04/2018 - 09:45
I'm sorry but for me to help you fix your formula, I would need to know what your formula looks like
User
08/04/2018 - 09:45
if they are above 40k total for the month it is 22%, if they are btw 40k and 70k it is 25%, if they are above 70% it is 30%. If they select snb i want to add a 10% uplift
User
08/04/2018 - 09:47
but if they sale commercial f they are above 40k total for the month it is 25%, if they are btw 40k and 70k it is 28%, if they are above 70% it is 35%. If they select snb i want to add a 10% uplift
User
08/04/2018 - 09:49
that is more like what I am wanting. There are 4 types of sales. existing customer and snb (new customers)
Excelchat Expert
08/04/2018 - 09:49
sorry you mean less than 40k or greater than 40k? between 40k and 70k is also greater than 40k
User
08/04/2018 - 09:49
less than
Excelchat Expert
08/04/2018 - 09:49
ok thank you
User
08/04/2018 - 09:50
this is what i was thought would work
User
08/04/2018 - 09:50
=IF(SCAR!F26<40000,D7*0.22,IF(AND(SCAR!F26>=40000,SCAR!F26<=70000),D7*0.25,D7*0.28))
User
08/04/2018 - 09:51
scar represents the total revenue and D7 is margin.
Excelchat Expert
08/04/2018 - 09:52
10% uplift for commercial as well?
User
08/04/2018 - 09:53
just commercial snb
User
08/04/2018 - 09:53
there are 4 types of sales
Excelchat Expert
08/04/2018 - 09:54
Oh I see, so only 10% uplift if the Sale is commercal snb
User
08/04/2018 - 09:54
or s&l snb
Excelchat Expert
08/04/2018 - 09:54
all others same forumula
User
08/04/2018 - 09:54
same formula just different %
Excelchat Expert
08/04/2018 - 09:55
Ok, for commercial formula is 25% for <40k? but the first formula you give is 22%?
Excelchat Expert
08/04/2018 - 09:56
So everything else has the same base formula except for Commercial?
Excelchat Expert
08/04/2018 - 09:56
the base formula is before adding the %uplift
User
08/04/2018 - 09:56
s&l would be 22% 25% and 30%
User
08/04/2018 - 09:56
commercail would be 25% 28% and 35%
Excelchat Expert
08/04/2018 - 09:56
ok got it
User
08/04/2018 - 09:56
if SNB behind it 10% uplift
User
08/04/2018 - 09:59
i can get it to pull up the calculation depending on the sale type but i don't know how to get it to follow the calculation on the row i am on if that makes sense
Excelchat Expert
08/04/2018 - 10:00
Alright, please give a moment to solve your problem. Thank you!
Excelchat Expert
08/04/2018 - 10:14
Sorry I'm still working with the formula, thanks for your patience
User
08/04/2018 - 10:14
not a problem at all.
Excelchat Expert
08/04/2018 - 10:21
Hi will it be ok to just add new column for the uplift?
User
08/04/2018 - 10:21
sure
Excelchat Expert
08/04/2018 - 10:26
ok to resolve your problem we use this formula
Excelchat Expert
08/04/2018 - 10:26
=if(isnumber(search("s&L",A2)),if(D2<40000,B2*22%,if(and(D2>=40000,D2<=70000),B2*25%,B2*30%)),if(isnumber(search("Commercial",A2)),if(D2<40000,B2*25%,if(and(D2>40000,D2<=700000),B2*28%,B2*35%))))
Excelchat Expert
08/04/2018 - 10:26
that is the base formula to compute s&l vs commercial
Excelchat Expert
08/04/2018 - 10:26
then this is the formula to get the %Uplift:
Excelchat Expert
08/04/2018 - 10:26
=if(or(A2="commercial snb",A2="s&L snb"),10%,"")
Excelchat Expert
08/04/2018 - 10:27
Then I just added the two in total commission column c: =F2+(F2*G2)
User
08/04/2018 - 10:27
so i can change the type of sale and it change the calculations
Excelchat Expert
08/04/2018 - 10:27
yes
Excelchat Expert
08/04/2018 - 10:28
you can try now
User
08/04/2018 - 10:28
awesome
Excelchat Expert
08/04/2018 - 10:28
Do you have any questions regarding the solution provided?
User
08/04/2018 - 10:28
I couldn't figure out how to put multiple arguments
User
08/04/2018 - 10:29
Thanks a bunch
User
08/04/2018 - 10:29
will for sure use you again if I get stuck
Excelchat Expert
08/04/2018 - 10:29
Thanks for using Got It Pro-Excel. Please give your kind feedback for our service. Have a good day! :)
Excelchat Expert
08/04/2018 - 10:32
Feel free to end this session as soon as you are ready for us to continue helping other customers. Thank you.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
