Return value for top 1/3 based off of two other columns?

[santafe]

Hi, I'm struggling with an Excel problem. I want to return values in column C based on columns A & B. If column A = "CV", then look at column B. If the value in Column B is in the top 1/3 of all CV, then return 2.5% in Column C. If it's in the middle 1/3 then return 2%, and if it's in the bottom 1/3 then return 1.5%. My column A has a list of different values "CV", "SS", "FS", and each one has different return values. So if it was FS and the top 1/3 of all FS's it would return 1.5% instead of 2.5%. Any help would be greatly appreciated!

A	B	C
CV	3.0	2.5%
CV	2.5	2%
CV	2.4	2%
CV	0.6	1.5%

 

[Eric W]

How do you mean the top 1/3? I can think of at least 4 ways to figure that. If the maximum value is 3.0, then the 3 ranges would be 0-1 for 1.5%, 1-2 for 2% and 2-3 for 2.5%, which is not what your example shows. Or if there is a theoretical maximum of 4, even if no one gets it, the ranges would be 0-1.33, 1.33 to 2.67, 2.67 to 4, which actually matches your example. Or you could figure the max value minus the min value and divide into ranges, or 3.0-.6 = 2.4. 2.4/3 = .8, so the ranges would be .6-1.4, 1.4 to 2.2, 2.2 to 3.0. Or we could say that we have n total scores, and anything below the n/3 lowest score gets 1.5%, anything over the n/3 highest score gets 2.5%, everyone else gets 2%.

 

[santafe]

Hi, I should have expanded the example. The number of rows in Column A will fluctuate between 10 and 15 rows. Column B is based off another formula and the values could equal for multiple rows (so in this case it could be 3.0, 3.0, 2.8, 2.7., 2.7, etc.). Based on your reply, I would need it to be divided into ranges. Thank you for clarifying that, as I'm realizing I needed to put more details versus saying just 1/3.

 

[Eric W]

See if this works for you:

Book1
	A	B	C
1
2	CV	3.0	0.025
3	CV	2.5	0.025
4	CV	2.4	0.02
5	CV	0.6	0.015
6	CV	1.0	0.02
7	CV	0.7	0.015
8	CV	2.2	0.02
9	FS	3.0
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=IF(A2<>"CV","",LOOKUP(B2,{0,0.01,0.01}+SMALL(IF($A$2:$A$20="CV",$B$2:$B$20),CHOOSE({1,2,3},1,COUNTIFS($A$2:$A$20,"CV")/3,ROUNDUP(COUNTIFS($A$2:$A$20,"CV")*2/3,0))),{0.015,0.02,0.025}))
Press CTRL+SHIFT+ENTER to enter array formulas.

Keep in mind that when you only have a small number of values, grouping them into 3 equal groups is problematical. But see if it works, and then we can look at how to incorporate the SS, FS, etc. values.

 

[santafe]

Wow, this worked perfectly! Thank you so much, this is incredible.