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.

 

[Peter_SSs]

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

Glad it worked for you.

I think the adjustments to Mike's formula could be
=IF(RANK(B1,B:B)<6,1-(RANK(B1,B:B)-1)*0.2, "")
or
=IF(RANK(B1,B:B)<6,1.2-RANK(B1,B:B)*0.2, "")

 

[Aladin Akyurek]

=(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.

This would mark the ties also.

 

[mikerickson]

Small typo

=IF(RANK(B1,B:B) < 6, 1-(5-RANK(B1,B:B))*.2, "")

 

[Peter_SSs]

Small typo

=IF(RANK(B1,B:B) < 6, 1-(5-RANK(B1,B:B))*.2, "")

Mike
I think there is more to it than that. For the OP's sample data that gives 0.2 for Helen who has the highest score & so should get "1" according to post #5 . Have you considered my suggestions in post #11 ?

 

[kipper19]

Peter,

I have noticed that as I have quiet a bit of data ie numbers in Column D that the formula will calculate the ranking over the whole column, how could I use a look up function to check Columns A & B then only average the data in Column D that corresponds to Column A & B etc

 

[Peter_SSs]

We can't copy the sample data from an image like that. Can you re-post the sample data and expected results in a form that we can copy to test with? The 'Look here' link in my signature block below has some suggestions for how you can do that.

 

[kipper19]

Peter,

I installed the addins VBHTML Maker into my excel addin folder but cannot get it work to post the file so don't worry to much, appreciate you trying to help

 

[Peter_SSs]

I installed the addins VBHTML Maker into my excel addin folder but cannot get it work to post the file so don't worry to much, appreciate you trying to help

You followed the wrong link in my signature. Follow 'Look here' not 'VBHTML Maker'

 

[kipper19]

Peter I can only post as this which I think can be copied Monday	1st	John	12	0.2
Monday	1st	Julie	14	0.4
Monday	1st	Jim	23	0.8
Monday	1st	Steve	8
Monday	1st	Helen	32	1
Monday	1st	Tom	9
Monday	1st	Tomas	8
Monday	1st	Paul	12	0.2
Monday	1st	Peter	15	0.6
Monday	1st	Jon	7
Monday	1st	Jerry	5
Tuesday	2nd	John	16	0.4
Tuesday	2nd	Julie	5
Tuesday	2nd	Jim	45	1
Tuesday	2nd	Steve	33	0.8
Tuesday	2nd	Helen	12	0.4
Tuesday	2nd	Tom	5
Tuesday	2nd	Tomas	7
Tuesday	2nd	Paul	6
Tuesday	2nd	Peter	22	0.6
Wednesday	3rd	John	8
Wednesday	3rd	Julie	9	0.2
Wednesday	3rd	Jim	23	1
Wednesday	3rd	Steve	21	0.8
Wednesday	3rd	Helen	11	0.4
Wednesday	3rd	Tom	2
Wednesday	3rd	Tomas	5
Wednesday	3rd	Paul	9	0.2
Wednesday	3rd	Peter	14	0.6
Wednesday	3rd	Jon	6
Wednesday	3rd	Jerry	3
Wednesday	3rd	Sam	6
Wednesday	3rd	Stan	6

<colgroup><col width="124" style="width: 93pt; mso-width-source: userset; mso-width-alt: 4534;"> <col width="64" style="width: 48pt;" span="4"> <tbody> </tbody>

 

[Peter_SSs]

Peter I can only post as this which I think can be copied

I can copy that but did you see post #18 ? The options make much neater screen shots.

Book1
	A	B	C	D	E
1	Monday	1st	John	12	0.2
2	Monday	1st	Julie	14	0.4
3	Monday	1st	Jim	23	0.8
4	Monday	1st	Steve	8
5	Monday	1st	Helen	32	1
6	Monday	1st	Tom	9
7	Monday	1st	Tomas	8
8	Monday	1st	Paul	12	0.2
9	Monday	1st	Peter	15	0.6
10	Monday	1st	Jon	7
11	Monday	1st	Jerry	5
12	Tuesday	2nd	John	16	0.4
13	Tuesday	2nd	Julie	5
14	Tuesday	2nd	Jim	45	1
15	Tuesday	2nd	Steve	33	0.8
16	Tuesday	2nd	Helen	12	0.2
17	Tuesday	2nd	Tom	5
18	Tuesday	2nd	Tomas	7
19	Tuesday	2nd	Paul	6
20	Tuesday	2nd	Peter	22	0.6
21	Wednesday	3rd	John	8
22	Wednesday	3rd	Julie	9	0.2
23	Wednesday	3rd	Jim	23	1
24	Wednesday	3rd	Steve	21	0.8
25	Wednesday	3rd	Helen	11	0.4
26	Wednesday	3rd	Tom	2
27	Wednesday	3rd	Tomas	5
28	Wednesday	3rd	Paul	9	0.2
29	Wednesday	3rd	Peter	14	0.6
30	Wednesday	3rd	Jon	6
31	Wednesday	3rd	Jerry	3
32	Wednesday	3rd	Sam	6
33	Wednesday	3rd	Stan	6
Top 5 (2)

Cell Formulas
Range	Formula
E1	=LOOKUP(SUMPRODUCT(($A$1:$A$33=A1)*($B$1:$B$33=B1)*(D1<$D$1:$D$33))+1,{1,2,3,4,5,6},{1,0.8,0.6,0.4,0.2,""})