# Tier rankings

#### picaman

##### New Member
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"

Jamie

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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

="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)

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

Jamie

Replies
1
Views
233
Replies
7
Views
133
Replies
6
Views
110
Replies
3
Views
204
Replies
9
Views
576

1,218,664
Messages
6,143,765
Members
450,503
Latest member
dforce

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back