Tier rankings

[picaman]

I have a series of 5 cells, each populated with numbers ranging from 0 to 200. I'd like for a 6th cell to be automatically populated with a phrase based on the following criteria:

3 or more cells with 95 or better = "Tier 1"
2 cells with 95 or better = "Tier 2"
1 cell with 95 or better = "Tier 3"
0 cells with 95 or better = "Tier 4"

Any ideas? Thanks in advance for your time and your input : )

Jamie

 

[dln]

Jamie,

It ain't pretty:
="Tier " & TEXT(4-IF(COUNTIF(B11:B16,">=95")>3, 3, COUNTIF(B11:B16,">=95")),"#")

If you can, I'd hide away the intermediate COUNTIF() calculation in a cell somewhere and refer to it (twice) within this formula.

Regards,
=dn

 

[Scott R]

="Tier "&CHOOSE(COUNTIF(A1:A5,">=95")+1,4,3,2,1,1,1)
OR
="Tier "&VLOOKUP(COUNTIF(A1:A5,">=95"),{0,4;1,3;2,2;3,1},2)

 

[picaman]

Thanks so much for all fo your help! This board is such an invaluable resource : )

Jamie