Need help to find correct formula! Multiple criteria and range levels

WawaLou

New Member
Joined
Sep 22, 2009
Messages
7
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! :)
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
I can only imagine what you have.
It would be better if you give complete examples of what you have and the results that are expected.

You could look for a solution with a helper table like the following:

varios 15feb2020.xlsm
DEFGHIJKL
1Mgr LevelVarianceSalesFormulaSales<=3%>3%
2General Mgr3%0.0%0 11.0%11.2%
3Asst Mgr4%0.0%75,001 12.0%12.2%
4General Mgr5%1.5%100,001 13.0%13.2%
5Asst Mgr6%1.5%125,001 14.0%14.2%
6Asst GM3%100,000 12.0%
7Asst GM4%65,000 11.2%
8Asst GM5%100,000 12.2%
9Asst GM6%155,000 14.2%
sheet
Cell Formulas
RangeFormula
G2:G9G2=IF(OR(D2={"General Mgr","Asst Mgr"}),IF(E2>=5%,1.5%,0),IF(D2="Asst GM",VLOOKUP(F2,$J$2:$L$6,IF(E2<=3%,2,3),1),0))
 
Upvote 0
Thank you so much for this...!

How would I handle the initial if(or) if there's more than 2 mgr levels? I actually have 7 (5 of them don't involve checking sales figures, it's just a vlookup to a large list of budget variances from -10% to 30%, and the other 2 need to be checked against sales.

I was thinking an index match function, but with the numerous mgr levels I'm stumped how to insert the if statement.
thanks again Dante!
 
Upvote 0
use a helper table as the second part of the formula.

As I said it would be better if you give full examples.

use XL2BB tool.
 
Upvote 0
Sorry for not including - here's the made up criteria I'm trying to recreate - pretty much like you had, but with a large data set for the bonus tables - this is a snipit but I think it looks clear.
Still trying to accomplish the same task - calculate estimated bonus's based on 2 groups of mgrs.
I'm not sure if the helper table works for my scenario; below is a sample of the bonus tree; but for each mgr area the variance table is broken down over 100 lines for each mgr section based on the Worse than budget to the Better than budget
Really appreciate your help!
AbbrevMgmnt LevelABBREVBUDGET VARIANCEBONUS %TYPEBeginning RangeBUDGET VARIANCEBONUS %
LSTGMLegacy Store TGMThese 5 have bonus based on variance to budget % x Bonus %LSTGM
-5.00%​
0.533%​
AGM
$0​
-5.00%​
0.533%​
LSGMLegacy Store GMThese 5 have bonus based on variance to budget % x Bonus %LSTGM
-2.50%​
0.553%​
AGM
$0​
-2.50%​
0.553%​
FSGMFuel Store GMThese 5 have bonus based on variance to budget % x Bonus %LSTGM
0.00%​
0.573%​
AGM
$0​
0.00%​
0.573%​
FSTGMFuel Store TGMThese 5 have bonus based on variance to budget % x Bonus %LSTGM
2.50%​
0.593%​
AGM
$0​
2.50%​
0.593%​
FBMFBMThese 5 have bonus based on variance to budget % x Bonus %LSTGM
5.00%​
0.613%​
AGM
$0​
5.00%​
0.613%​
AGMAGMThese 2 have bonus based on variance to budget % AND sales level of the store x Bonus %LSTGM
7.50%​
0.633%​
AGM
$0​
7.50%​
0.633%​
GMITGMITThese 2 have bonus based on variance to budget % AND sales level of the store x Bonus %LSGM
-5.00%​
0.653%​
AGM
$25,000​
-5.00%​
0.004%​
LSGM
-2.50%​
0.673%​
AGM
$25,000​
-2.50%​
0.024%​
LSGM
0.00%​
0.693%​
AGM
$25,000​
0.00%​
0.044%​
LSGM
2.50%​
0.713%​
AGM
$25,000​
2.50%​
0.064%​
LSGM
5.00%​
0.733%​
AGM
$25,000​
5.00%​
0.084%​
MGR LEVELBudget VARIANCESALES Budget sales VarianceFormulaFSGM
-5.00%​
0.753%​
AGM
$25,000​
7.50%​
0.104%​
LSTGM
$10,000​
FSGM
-2.50%​
0.773%​
GMIT
$0​
-5.00%​
0.533%​
LSGM
$20,000​
FSGM
0.00%​
0.793%​
GMIT
$0​
-2.50%​
0.553%​
FSGM
$30,000​
FSGM
2.50%​
0.813%​
GMIT
$0​
0.00%​
0.573%​
FSTGM
$40,000​
FSGM
5.00%​
0.833%​
GMIT
$0​
2.50%​
0.593%​
FBM
$50,000​
FSTGM
-5.00%​
0.853%​
GMIT
$0​
5.00%​
0.613%​
AGM
$10,000​
$15,000​
66.67%​
FSTGM
-2.50%​
0.873%​
GMIT
$0​
7.50%​
0.633%​
GMIT
($1,000)​
$26,000​
-3.85%​
FSTGM
0.00%​
0.893%​
GMIT
$25,000​
-5.00%​
0.004%​
FSTGM
2.50%​
0.913%​
GMIT
$25,000​
-2.50%​
0.024%​
FSTGM
5.00%​
0.933%​
GMIT
$25,000​
0.00%​
0.044%​
FBM
-5.00%​
0.953%​
GMIT
$25,000​
2.50%​
0.064%​
FBM
-2.50%​
0.973%​
GMIT
$25,000​
5.00%​
0.084%​
FBM
0.00%​
0.993%​
GMIT
$25,000​
7.50%​
0.104%​
FBM
2.50%​
1.013%​
FBM
5.00%​
1.033%​
 
Upvote 0
PS - Sorry about not using XL2BB tool - it is not one of my available add-ons in Excel 365 (may be blocked by our IT dept)
 
Upvote 0
Sorry about not using XL2BB tool

Do not worry, with copy and paste the values here we can make a review.

here's the made up criteria I'm trying to recreate

Now, to understand how to apply those criteria, you could explain with values and results what you need, that is, instead of putting this:
"IF Mgr level = General Manager or Asst Mgr and their budget % is .5% Better"
You could put examples with real data and explain what data in the table corresponds to the result.

You try to put the largest number of scenarios so that what you require is more compressible.
 
Upvote 0
I thought I did with the info I listed out. There are 7 mgr groups, 5 of them come from table 1 (Headers=ABBREV, Budget Variance, Bonus %), and the other 2 mgr levels come from table 2 (Headers=TYPE, Beginning (Range),Budget Variance and Bonus %). There are calculations in my spreadsheet that get me to the Budget Variance (And Budget Variance%), I just need to marry up the Mgrs along with the two tables.
For example
IF Mgr Level = LSTGM, LSGM, FSGM, or FBM, then if True find their Actual Budget Variance % in the Budget Variance column of Table 1, and then bring over the corresponding Bonus % so it can be multiplied by their store budget to come to equal their final estimated bonus.
IF Mgr Level = AGM, GMIT, then if True find their Actual Budget Variance % AND their Actual Sales figure, and find the corresponding values in Table 2, and then bring over the corresponding Bonus % so it can be multiplied by their store budget to equal their final estimated bonus.
 
Upvote 0
Sorry, but I am not understanding what your example is, what your table is and what your result is.

In my example, I put values, the result of the formula and what the table is.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top