Ranking associate scores with meet a minimum number of survey results

[longhornlongcut]

Here is a sample of my data. I am trying to rank associates by their SQscore as long as they meet the minimum number of surveys returned (SQ Survey Count). The minimum number is based off the average number of surveys returned, 13.8.

What I hope to see is =Rank associate by their SQ score, but only if they have at least 13.8 survey results, other wise show "-".

SQ Avg	13.8
ID	SQ Survey Count	SQ Score	Rank
ASZYMEL	30	60
CLISZKA	2	50
CPRICE	1	-100
DOMALLEY	11	18
EDULZO	2	0
JHUBACEK	30	77
JKANAK	1	100
JOMALLEY	2	100
JPDELACRUZ	12	25
KBONAMER	39	62
KSHERMAN	43	51
MB4570	1	100
MNAVARRETE	3	33
MURIBE	1	100
NHARRIS	29	24

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

 

[DRSteele]

Welcome to the forum.

What you seek is a formula that ranks with conditions. There is no Excel function yet that does RANKIF, but we can hope the Excel team creates one soon.

This topic is covered extensively in the forum, but I worked out the answer for you anyway. There is a single-cell formula and there is an algorithm that requires a helper column (since function RANK chokes on complex arrays).

Copy D4, F4 and G4 down to cover the entire range.

	A	B	C	D	E	F	G
1	SQ Avg	13.8
2
3	ID	SQ Survey Count	SQ Score	RankIf		SQScore >=13.8	Rank
4	ASZYMEL	30	60	3		60	3
5	CLISZKA	2	50	-		FALSE
6	CPRICE	1	-100	-		FALSE
7	DOMALLEY	11	18	-		FALSE
8	EDULZO	2	0	-		FALSE
9	JHUBACEK	30	77	1		77	1
10	JKANAK	1	100	-		FALSE
11	JOMALLEY	2	100	-		FALSE
12	JPDELACRUZ	12	25	-		FALSE
13	KBONAMER	39	62	2		62	2
14	KSHERMAN	43	51	4		51	4
15	MB4570	1	100	-		FALSE
16	MNAVARRETE	3	33	-		FALSE
17	MURIBE	1	100	-		FALSE
18	NHARRIS	29	24	5		24	5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet16​

Worksheet Formulas Cell Formula D4 =IF(B4>=$B$1,SUMPRODUCT(--($B$4:$B$18>=$B$1),--(C4<$C$4:$C$18))+1,"-") G4 =IF(F4,RANK.EQ(F4,$F$4:$F$18),"") F3 ="SQScore >="&B1 F4 =IF(B4>=$B$1,C4) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[uacdub]

thank you so much. This worked exactly like I wanted it too.