Trying to come up with a formula for a Sandbox that calculates an employee bonus's
Criteria 1 - Type of Mgr - These 2 levels receive bonus based on % variance to budget
* General Mgr
* Asst Mgr
Criteria 2 - Type of Mgr - These 2 levels receive bonus based on Store Level Sales (4 levels of Low to High Ranges) AND % variance to budget
For instance if their store level sales are $0 to $75000 and their variance to budget is 3% they receive 11%, if 3.25% they receive 11.2%, with a long level of ranges from $75,000 up to $300,000
Sequentially the formula is "IF Mgr level = General Manager or Asst Mgr and their budget % is .5% Better than budget they get 1.5% Bonus, but if the mrg level is Asst GM AND their store sales are $0-$75000, $75001-$10000, $100001-$125000 AND THEIR Budget % is 3% better they get X% Bonus" ...
Having a heck of a time trying to figure out this one - I'm thinking combo of IFS with Index/Match - but I'm STUMPED!
Please HELP!
Criteria 1 - Type of Mgr - These 2 levels receive bonus based on % variance to budget
* General Mgr
* Asst Mgr
Criteria 2 - Type of Mgr - These 2 levels receive bonus based on Store Level Sales (4 levels of Low to High Ranges) AND % variance to budget
For instance if their store level sales are $0 to $75000 and their variance to budget is 3% they receive 11%, if 3.25% they receive 11.2%, with a long level of ranges from $75,000 up to $300,000
Sequentially the formula is "IF Mgr level = General Manager or Asst Mgr and their budget % is .5% Better than budget they get 1.5% Bonus, but if the mrg level is Asst GM AND their store sales are $0-$75000, $75001-$10000, $100001-$125000 AND THEIR Budget % is 3% better they get X% Bonus" ...
Having a heck of a time trying to figure out this one - I'm thinking combo of IFS with Index/Match - but I'm STUMPED!
Please HELP!