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

[WawaLou]

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!

 

[DanteAmor]

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
	D	E	F	G	H	I	J	K	L
1	Mgr Level	Variance	Sales	Formula			Sales	<=3%	>3%
2	General Mgr	3%		0.0%			0	11.0%	11.2%
3	Asst Mgr	4%		0.0%			75,001	12.0%	12.2%
4	General Mgr	5%		1.5%			100,001	13.0%	13.2%
5	Asst Mgr	6%		1.5%			125,001	14.0%	14.2%
6	Asst GM	3%	100,000	12.0%
7	Asst GM	4%	65,000	11.2%
8	Asst GM	5%	100,000	12.2%
9	Asst GM	6%	155,000	14.2%
sheet

Cell Formulas
Range	Formula
G2:G9	G2	=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))

 

[WawaLou]

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!

 

[DanteAmor]

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.

 

[WawaLou]

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!

Abbrev

Mgmnt Level

ABBREV

BUDGET VARIANCE

BONUS %

TYPE

Beginning Range

BUDGET VARIANCE

BONUS %

LSTGM

Legacy Store TGM

These 5 have bonus based on variance to budget % x Bonus %

LSTGM

-5.00%​

0.533%​

AGM

$0​

-5.00%​

0.533%​

LSGM

Legacy Store GM

These 5 have bonus based on variance to budget % x Bonus %

LSTGM

-2.50%​

0.553%​

AGM

$0​

-2.50%​

0.553%​

FSGM

Fuel Store GM

These 5 have bonus based on variance to budget % x Bonus %

LSTGM

0.00%​

0.573%​

AGM

$0​

0.00%​

0.573%​

FSTGM

Fuel Store TGM

These 5 have bonus based on variance to budget % x Bonus %

LSTGM

2.50%​

0.593%​

AGM

$0​

2.50%​

0.593%​

FBM

FBM

These 5 have bonus based on variance to budget % x Bonus %

LSTGM

5.00%​

0.613%​

AGM

$0​

5.00%​

0.613%​

AGM

AGM

These 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%​

GMIT

GMIT

These 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 LEVEL

Budget VARIANCE

SALES

Budget sales Variance

Formula

FSGM

-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%​

 

[WawaLou]

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)

 

[DanteAmor]

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.

 

[WawaLou]

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.

 

[DanteAmor]

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.