I have been searching for a solution to this for a couple of days and I am trying to determine if I can use a standard if Formula, if I need to include a vlookup/lookup or some other formula as the only way I have been able to get my formula to work is if I type in the "goals" (will be explained below) manually into the formula, rather than having the formula pull the data from the cell.
To simply: I have three sales groups where sales are combined for overall attainment, bonus is achieved based on the total sales volume and is paid on the attainment of each group. ie. Group A = $200 Group B = $400 Group C = $600. Total attainment is 1,200, however each group would be paid a bonus based on the group they are in. To make things less complex I will be using a separate if formula for each group.
I have five attainment (bonus) tiers. ie.
<=$300 = 1% for group A, 2% for group B, 3% for group C,
301-$600 = 2% for group A, 3% for Group B, 4 % for group C
...
...
>=$1201 = 5% for group A, 6% for Group B, 7% for Group C
Under this scenario the overall attained sales is $1200. Now comes the tricky part. I am trying to get the formula to work where I first use Group A. (Very simplified) If Overall salesTier 1<=Tier 2, $200* 2%, etc etc.
I would then write a formula for groups B and C separately in the same fashion.
What formulas would you use to make this function work?
Attached: Example.
Bookmarks