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 "-".

[TABLE="width: 271"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]SQ Avg[/TD]
[TD="align: right"]13.8[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]SQ Survey Count[/TD]
[TD]SQ Score[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]ASZYMEL[/TD]
[TD]30[/TD]
[TD]60[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CLISZKA[/TD]
[TD]2[/TD]
[TD]50[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CPRICE[/TD]
[TD]1[/TD]
[TD]-100[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]DOMALLEY[/TD]
[TD]11[/TD]
[TD]18[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]EDULZO[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]JHUBACEK[/TD]
[TD]30[/TD]
[TD]77[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]JKANAK[/TD]
[TD]1[/TD]
[TD]100[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]JOMALLEY[/TD]
[TD]2[/TD]
[TD]100[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]JPDELACRUZ[/TD]
[TD]12[/TD]
[TD]25[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]KBONAMER[/TD]
[TD]39[/TD]
[TD]62[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]KSHERMAN[/TD]
[TD]43[/TD]
[TD]51[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]MB4570[/TD]
[TD]1[/TD]
[TD]100[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]MNAVARRETE[/TD]
[TD]3[/TD]
[TD]33[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]MURIBE[/TD]
[TD]1[/TD]
[TD]100[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NHARRIS[/TD]
[TD]29[/TD]
[TD]24[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[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
ASZYMEL
CLISZKA
CPRICE
DOMALLEY
EDULZO
JHUBACEK
JKANAK
JOMALLEY
JPDELACRUZ
KBONAMER
KSHERMAN
MB4570
MNAVARRETE
MURIBE
NHARRIS

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FCE4D6"]SQ Avg[/TD]
[TD="align: right"]13.8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFF2CC"]ID[/TD]
[TD="bgcolor: #FFF2CC"]SQ Survey Count[/TD]
[TD="bgcolor: #FFF2CC"]SQ Score[/TD]
[TD="bgcolor: #FFF2CC"]RankIf[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"]SQScore >=13.8[/TD]
[TD="bgcolor: #FFF2CC"]Rank[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]60[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]60[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]50[/TD]
[TD="bgcolor: #C6E0B4"]-[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]
[TD="bgcolor: #C6E0B4"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]-100[/TD]
[TD="bgcolor: #C6E0B4"]-[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]
[TD="bgcolor: #C6E0B4"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]18[/TD]
[TD="bgcolor: #C6E0B4"]-[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]
[TD="bgcolor: #C6E0B4"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: #C6E0B4"]-[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]
[TD="bgcolor: #C6E0B4"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]77[/TD]
[TD="bgcolor: #C6E0B4, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]77[/TD]
[TD="bgcolor: #C6E0B4, align: right"]1[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: #C6E0B4"]-[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]
[TD="bgcolor: #C6E0B4"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: #C6E0B4"]-[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]
[TD="bgcolor: #C6E0B4"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]25[/TD]
[TD="bgcolor: #C6E0B4"]-[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]
[TD="bgcolor: #C6E0B4"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]39[/TD]
[TD="align: right"]62[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]62[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]43[/TD]
[TD="align: right"]51[/TD]
[TD="bgcolor: #C6E0B4, align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]51[/TD]
[TD="bgcolor: #C6E0B4, align: right"]4[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: #C6E0B4"]-[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]
[TD="bgcolor: #C6E0B4"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]33[/TD]
[TD="bgcolor: #C6E0B4"]-[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]
[TD="bgcolor: #C6E0B4"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: #C6E0B4"]-[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]
[TD="bgcolor: #C6E0B4"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]24[/TD]
[TD="bgcolor: #C6E0B4, align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]24[/TD]
[TD="bgcolor: #C6E0B4, align: right"]5[/TD]

</tbody>

Sheet16​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]=IF(B4>=$B$1,SUMPRODUCT(--($B$4:$B$18>=$B$1),--(C4<$C$4:$C$18))+1,"-")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G4[/TH]
[TD="align: left"]=IF(F4,RANK.EQ(F4,$F$4:$F$18),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F3[/TH]
[TD="align: left"]="SQScore >="&B1[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F4[/TH]
[TD="align: left"]=IF(B4>=$B$1,C4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[uacdub]

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