Finding the highest number

[kipper19]

Hi all,
Looking for some help on a formula to find the highest values in a range then returning a 1 against the name.

 

[mikerickson]

In C1, put the formula =IF(RANK(B1, B:B) < 6, 1, "") in C1 and drag down.

 

[Dave Patton]

=(B1>=LARGE($B$1:$B$10,5))+0 or

=IF((B1>=LARGE($B$1:$B$10,5)),1,"")

copy down

N.B. My post does not adjust down if there are ties; therefore edit or use Mike's.

 

[kipper19]

Mike / Dave. appreciate your help, this forum never ceases to amaze me on the help you get

 

[Peter_SSs]

If you are just interested in identifying/looking at those 'top 5' rows, another option (assuming your data actually has headings) is to apply AutoFilter to your data (or at least column B) and then in the column B AutoFilter drop-down, choose
Number Filters -> Top 10 ... -> Change the boxes to Top 5 Items -> OK
That would then filter your data to only display the top 5 items (plus ties).

 

[kipper19]

Mike / Dave, both formulas work great, one question if I wanted to give these top 5 numbers a different points rating, how could I do that.

Like

Highest = 1
2nd Highest = 0.8
3rd Highest = 0.6
4th Highest = 0.4
5th Highest = 0.2

 

[mikerickson]

=if(rank(b1,b:b)<6,1-(5-rank(b1:b:b))*.2, "")

 

[kipper19]

Hi Mike, appreciate your time in replying although I get an error "You've entered too few arguments for this function"

 

[Peter_SSs]

Mike / Dave, both formulas work great, one question if I wanted to give these top 5 numbers a different points rating, how could I do that.

Like

Highest = 1
2nd Highest = 0.8
3rd Highest = 0.6
4th Highest = 0.4
5th Highest = 0.2

Try this, copied down. You haven't identified what to do when ties occur so this is a guess in that regard.

Book1
	A	B	C
1	John	12
2	Julie	14
3	Jim	23	0.8
4	Syeve Ryan	8
5	Helen	32	1
6	Tom	9
7	Tomas	8
8	Paul	12
9	Peter	15	0.2
10	Julie	18	0.4
11	June	23	0.8
Top 5

Cell Formulas
Range	Formula
C1	=LOOKUP(RANK(B1,B$1:B$11),{1,2,3,4,5,6},{1,0.8,0.6,0.4,0.2,""})

 

[kipper19]

Thank you kindly Peter and to all for your help, works well