Look up high and low values, then define them

[pacerfan07]

Hi,

I'm trying to take a list of values in Column A, then in Column B define which ones are "high ranked" and which are "low ranked" So if I have the table shown below, I 'd like to take the top two values in column A and in column B label those as "high" and the bottom two should show as "low". The middle two are "avg." Thanks!

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]80[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

Try

=IF(A1>=LARGE($A$1:$A$6,2),"High",IF(A1<=SMALL($A$1:$A$6,2),"low","avg"))

 

[pacerfan07]

worked perfect thanks!

 

[Scott T]

You're welcome