top rated formula

[thebeans]

hi all
what I have is a set of horse racing races with some ratings.(column H)for each race I wont to know which horse is top rated(column H which are green filled).at the moment I can work out the top rated from 1 race at a time with =IF(H5=MAX($H$5:$H$11),"MAX1",""). but what I would like to happen is to try and get it to work out all the races,as I have to do all the races 1 by 1.is this possible at all
thanks richard

Book3 - Jumpshare

 

[jarjarbingie]

Re: top rated formula help

hi all
what I have is a set of horse racing races with some ratings.(column H)for each race I wont to know which horse is top rated(column H which are green filled).at the moment I can work out the top rated from 1 race at a time with =IF(H5=MAX($H$5:$H$11),"MAX1",""). but what I would like to happen is to try and get it to work out all the races,as I have to do all the races 1 by 1.is this possible at all
thanks richard

Book3 - Jumpshare

J2=IF(ISNUMBER(SEARCH("Race",A2)),A2,J1)
K2=IF($H2=MAX(IF(($J$2:$J$221=J2)*(ISNUMBER($H$2:$H$221)),$H$2:$H$221,"")),"Max","") press CTRL+SHIFT+ENTER

 

[thebeans]

Re: top rated formula help

hi thanks for the response. column J is just showing the formula and column K iam not getting anything when that is copyed and pasted down
thanks richard

 

[jarjarbingie]

Re: top rated formula help

hi thanks for the response. column J is just showing the formula and column K iam not getting anything when that is copyed and pasted down
thanks richard

it seems your column J is formatted as Text, change it to General, then re-enter the formula..

 

[thebeans]

Re: top rated formula help

thanks jarjarbingie you was right that was my fault. my column K is showing up with nothing, not sure why

 

[Peter_SSs]

Re: top rated formula help

Using just a single column, try this in J5, copied down.

Excel Workbook
	D	E	F	G	H	I	J
1
2
3
4	Jockey	Bar	Prize	Odds	Final Rating	finished place
5	A Lynch	1	5825	51.00	-5.7
6	N Callow	3	15915	1.70	23.2	1st	Max
7	B Thornton	9	3775	31.00	-4.4
8	J Noonan	7	3825	6.50	19.3
9	J Bayliss	6	3675	19.00	18.6
10	R Leod	5	6925	3.10	10.1	2nd
11	J Beriman	2	2725	10.00	8.7	3rd
12
13
14	Jockey	Bar	Prize	Odds	Final Rating
15	A Lynch	10	4300	61.00	-3.1
16	B Werner	7	6630	23.00	0.5
17	C Symons	11	1125	81.00	3.1
18	C Caserta	5	0	26.00
19	N Callow	4	4750	13.00	4.2
20	B Allen	8	10810	1.75	30.3	1st	Max
21	J Neil	1	1475	5.00	9
22	J Bates	9	1600	6.00	20.3	2nd
23	J Bayliss	6	1700	14.00	-52.9
24	W Egan	3	0	16.00
25	A Mallyon	2	1575	26.00	5.5	3rd
26
27
28	Jockey	Bar	Prize	Odds	Final Rating
29	J Mott	5	10435		27.8	3rd	Max
30	B Stockdale	8	5840		-5.2
Sheet5

 

[thebeans]

Re: top rated formula help

thanks peter that is excellent,you have saved me lots of time.there is one little problem and that is if there is no jockey column D it puts another max in,but i can live with that
thanks again richard

 

[Peter_SSs]

Re: top rated formula help

thanks peter that is excellent,you have saved me lots of time.

You are welcome.

there is one little problem and that is if there is no jockey column D it puts another max in,

Is there another column that has at least one empty row between races and always has a value (eg Horse or Bar or Odds) even if there is no jockey? If so, use that column where I have used "D" in my formula.

 

[thebeans]

Re: top rated formula help

thanks peter that is excellent,you have saved me lots of time.there is one little problem and that is if there is no jockey column D it puts another max in,but i can live with that
thanks again richard

is there a way to make it count Max1,Max2 and soo on
thanks richard

 

[Peter_SSs]

Re: top rated formula help

is there a way to make it count Max1,Max2 and soo on

What do you mean?