Vlookup, If, Or and And Formula help.

M

mkellycole

New member
Joined
Nov 12, 2015
Messages
24
Reaction score
0
Points
0
Excel Version(s)
2016
I want to calculate from a variable commission pool, the bonus for each individual based on the factors of responsibility and rate. Attached Table. The total bonus pool can vary based on sales and the persons level could change as well as the responsibility level but the bonus must dilute the variable amount and no more. Not sure how to accomplish this.
 

Attachments

  • Formula1.xlsx
    10.3 KB · Views: 17
mkellycole said:
Not sure how to accomplish this.
Click to expand...
If I understood you well, try the following formula in cell E4
Code: 
=($E$3*VLOOKUP(D4,$B$13:$C$16,2,FALSE))/100
 

Attachments

  • mkellycole-navic.xlsx
    11.2 KB · Views: 3
Thank you.

We're close. The formula is not using the entire balance of the pool. I don't think the values are correct for the multiplier to calculate the bonus. It should what ever the rating allows and use all funds in the pool. A 1 rating is lower than a 4 rating in both tables, responsibility and commission level. I may be missing something.

navic said:
If I understood you well, try the following formula in cell E4
Code: 
=($E$3*VLOOKUP(D4,$B$13:$C$16,2,FALSE))/100
Click to expand...
 
Please don't quote entire posts. They're just clutter and make thread hard to read. Thx

Perhaps adding expected results to your sheet would help us help you
 
mkellycole said:
We're close. ............I may be missing something.
Click to expand...
Can you in my example file write in E4, E5, etc. ... the expected results for all options
 
I have added comments in the new formula worksheet. Thank you.
 

Attachments

  • Formula1.xlsx
    14 KB · Views: 12
You must log in or register to reply here.
Back
Top