Multiple variable formula

[jaybird2569]

Good morning,
I have a sales sheet that I use to calculate goals based on the seniority level of each salesperson. The formula checks the amount in column "D" against the appropriate goals in "D1-D15" and returns a rating. Currently I can't just drag the formula down the entire column as there are different levels and goals. I have to edit each formula based on seniority levels. What I would like to do is have the formula check in column "C" for the level then, based on that level, use the appropriate formula. Any help is greatly appreciated.
Here is an example of my sheet.

https://drive.google.com/open?id=0BzCAijneXT6JR3VsTDRmM3RRZTRBV21KVExrMGExTzJuVS1B

 

[jtakw]

Hi,

Wish you had either provided a Table that we can copy into Excel to work with, or had uploaded the file Rather than just a Picture.

You'll need to make changes to the Levels Percentage Tables (L1, L2, L3, etc.) as in my Sample below, use E23 formula copied down for All your data:

Book1
	A	B	C	D	E
1			L1	0%
2				63.76%
3				70.00%
4				75.01%
5			L2	0.00%
6				65.46%
7				72.00%
8				77.01%
9			L3	0.00%
10				67.16%
11				74.00%
12				79.01%
13			L4	0.00%
14				68.86%
15				76.00%
16				81.01%
17			L5	0.00%
18				70.56%
19				78.00%
20				83.01%
21
22	Month	Name	Seniority Level	Sales Goal	Rating
23	January	John	L1	71.00%	3
Sheet69

Cell Formulas
Range	Formula
E23	=IF(D23="","",IF(D23="N/A",3,LOOKUP(D23,CHOOSE(0+RIGHT(C23,1),D1:D4,D5:D8,D9:D12,D13:D16,D17:D20),{1,2,3,4})))

 

[jaybird2569]

Thank you jtakw!
I apologize for not uploading the file or making a table, I couldn't figure out how to do that. This works perfectly and I understand adding the 0.00% to the lookup and re-ordering the goals. Could you please explain the

(D23,CHOOSE(0+RIGHT(C23,1)

portion of the formula? This may come in very handy with other projects but would like to understand it better in order to adapt it properly.
Thank you again.

 

[jtakw]

You're welcome.

About posting samples:
Have a look at Item B here: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html
and Post #2 here: https://www.mrexcel.com/forum/about-board/508133-attachments.html

To answer your question above:
The CHOOSE function needs an "Index" number to decide which "value" to return (in this case, which Cell Range to use for the LOOKUP), for that, I used the RIGHT function to extract from D23, the number "1" within "L1" (or 2 if it's L2, 3 if it's L3, etc.), however, the extraction returns a TEXT value, not a Real Number, adding 0 to it, converts it to a Real Number that the CHOOSE function would recognize.

 

[jaybird2569]

I will use those links on my next post to make it easier to understand.
Thank you for explaining that formula, makes much more sense.

 

[jtakw]

You're welcome.

To answer your question above:
The CHOOSE function needs an "Index" number to decide which "value" to return (in this case, which Cell Range to use for the LOOKUP), for that, I used the RIGHT function to extract from D23, the number "1" within "L1" (or 2 if it's L2, 3 if it's L3, etc.), however, the extraction returns a TEXT value, not a Real Number, adding 0 to it, converts it to a Real Number that the CHOOSE function would recognize.

You're welcome, as highlighted in Red above, I meant C23. typo

 

[jaybird2569]

I'll try to paste a table in here for clarification. Everything works great as you wrote it. I have one more column that counts backwards, the lower the number of days the better the rating. I tried putting the larger number at the top and also flipped the result_vector from 1,2,3,4 to 4,3,2,1 but it's not working correctly. If you can, see what I have wrong please.

[TABLE="width: 419"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]>=20.7[/TD]
[TD="align: right"]1[/TD]
[TD]L1[/TD]
[TD="align: right"]20.70[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18.00 - 20.69[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]18.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16.10 - 17.99[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]16.09[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.00 - 16.09[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]L2[/TD]
[TD="align: right"]19.55[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]The lower the number[/TD]
[TD][/TD]
[TD="align: right"]17.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]the better the rating[/TD]
[TD][/TD]
[TD="align: right"]14.99[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]L3[/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13.99[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Levels 3 - 5[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]have the same goals[/TD]
[TD][/TD]
[TD="align: right"]18.40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]L4[/TD]
[TD="align: right"]18.40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13.99[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]L5[/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13.99[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18.40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD]Name[/TD]
[TD]Seniority Level[/TD]
[TD]Days[/TD]
[TD]Rating[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]John[/TD]
[TD]L1[/TD]
[TD="align: right"]16.10[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]Beth[/TD]
[TD]L4[/TD]
[TD]n/a[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]Fred[/TD]
[TD]L2[/TD]
[TD="align: right"]15.65[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]Sam[/TD]
[TD] L1[/TD]
[TD="align: right"]13.50[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]Tammy[/TD]
[TD]L3[/TD]
[TD="align: right"]17.00[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]Steven[/TD]
[TD]L5[/TD]
[TD="align: right"]10.21[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

Here's the formula I have in E23-E28

=IF(D23="","",IF(D23="N/A",3,LOOKUP(D23,CHOOSE(0+RIGHT(C23,1),$D$1:$D$4,$D$5:$D$7,$D$8:$D$12,$D$13:$D$16,$D$17:$D$20),{1,2,3,4})))

 

[jtakw]

Hi,

If I understand correctly, try it like my sample below, when testing for correct results, Pay Special Attention to the Cut Off points Between Ratings for Each Level -- (See where I changed your Rating 3 from 16.09 to 16.10 for Level 1)-- I did this because you've shown the logic for That level in your post, but you didn't show the logic for Levels 2 and 3, so I can't be sure it'll produce the correct results at the cutoff points.

Book1
	A	B	C	D	E
1			L1	0.00
2				16.10
3				18.00
4				20.70
5			L2	0.00
6				14.99
7				17.00
8				19.55
9			L3	0.00
10				13.99
11				16.00
12				18.40
13			L4	0.00
14				13.99
15				16.00
16				18.40
17			L5	0.00
18				13.99
19				16.00
20				18.40
21
22	Month	Name	Seniority Level	Days	Rating
23	January	John	L1	16.10	3
24	January	Beth	L4	N/A	3
25	January	Fred	L2	15.65	3
26	January	Sam	L1	13.50	4
27	January	Tammy	L3	17.00	2
28	January	Steven	L5	10.21	4
Sheet78

Cell Formulas
Range	Formula
E23	=IF(D23="","",IF(D23="N/A",3,LOOKUP(D23,CHOOSE(0+RIGHT(C23,1),D$1:D$4,D$5:D$8,D$9:D$12,D$13:D$16,D$17:D$20),{4,3,2,1})))

Formula copied down, let me know if we need to tweak the formula...

 

[jaybird2569]

That worked. I had the formula correct just didn't change the cut-off points correctly. Thanks again for all of your assistance and explanations. Have a great weekend!

 

[jtakw]

You're welcome, jaybird.

Please note, the "lookup vector" within the LOOKUP function Has to be in Ascending order (lowest to highest) to work properly, I see in you Post #7 , you had L1, L2, L4 backwards..., just so you know for future reference.