Identify top 3, bottom 3 and intermediate values using IF function

**nataliebenjamin** · 11-17-2012, 02:20 PM

Hi Everyone,

I need to identify top 3, bottom 3 and intermediate values for each column in a table A and based on the same, return a value of HIGH, LOW or INTERMEDIATE to corresponding cells in table B. I have been asked to use the IF function.

I have attached a sample excel sheet and manually entered values in the first coulmn of table B to give a better understanding of the results that I need.

I appreciate your help!!

Natalie

**Ace_XL** · 11-17-2012, 02:37 PM

In D19 try..

=IF(RANK(D5,$D$5:$D$14)<=3,"high",IF(RANK(D5,$D$5:$D$14)>=7,"Low","Intermediate"))

Copy across and down

**e.isayev** · 11-17-2012, 03:21 PM

In e19

=if(e5>=large($e$5:$e$14,3),"high",if(e5<=small($e$5:$e$14,3),"low","intermediate"))

**e.isayev** · 11-17-2012, 03:28 PM

Difference between my and ACE_XL"s formula is.
If there same numbers (ex 2 10s) large function will take both of them. But rank function will rank only number of 10.
According to your spreadsheet formula with Rank function is what you want.