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

 

[thebeans]

Re: top rated formula help

what iam trying to do is work out how many times the top rated in each race came 1st, 2nd,3rd. the way i was doing it is by manually doing through all the races and marking the top rated with Max1 then the next race top rated max2 and soo on.then another sheet takes that data(location and where it placed)in list form, then all data goes to another sheet where i can easy read it.hope that makes sense.
i did try to change column D over to column F but for some reason when it gets to row 175 it puts the max in between the races (two blank cells between races)
thanks richard

 

[Peter_SSs]

Re: top rated formula help

what iam trying to do is work out how many times the top rated in each race came 1st, 2nd,3rd. the way i was doing it is by manually doing through all the races and marking the top rated with Max1 then the next race top rated max2 and soo on.then another sheet takes that data(location and where it placed)in list form, then all data goes to another sheet where i can easy read it.hope that makes sense.

Not enough sense I'm afraid. Could you post, say, 3 races worth of rows with the expected results you are trying to describe? My signature block below has a link for methods of posting directly in the thread like I did in post 6, which makes it easy to see and copy.

i did try to change column D over to column F but for some reason when it gets to row 175 it puts the max in between the races (two blank cells between races)

There are 2 blank cells between races in my post 6 sample and the formula did not produce anything in those rows. I also tested using column F in your sample file (over 200 rows) and the formula worked fine. Please post the formula from the first row as you adjusted it.

 

[thebeans]

Re: top rated formula help

Sheet1

	A	B	C	D	E	F	G	H	I	J
Cranbourne Race # 1 - Friday, 10 March 2017 6:30pm		P
Form Guide: Walk For Phil Maiden Plate - 2025m
Last 10	Horse	Trainer	Jockey	Bar	Prize	Final Rating
68x8678895	Mighty Mo	Mark C Webb	A Lynch
220680x5322	Baykool	Patrick Carey	N Callow
6390x7	Kalalo	Matthew Brown	B Thornton
Courte Choux	Henry Dwyer	J Noonan
70x75965	Eloquent Belle	Robbie Laing	J Bayliss
6080x536	Kamili	Patrick Carey	R Leod
0x097x3	Miss Cavatica	Ms J Beriman	J Beriman
Cranbourne Race # 2 - Friday, 10 March 2017 7:00pm		P
Form Guide: Ferntree Gully Motor Group Maiden - 1200m
Last 10	Horse	Trainer	Jockey	Bar	Prize	Final Rating
8000x8727x	Commanding Joy	Mark C Webb	A Lynch
48x608x045	Falsified	Gary McGreal	B Werner
7x8	Serra Da Estrela	T J Glasson	C Symons
	That's Dancing	Margot McDougal	C Caserta
483x409x	We Are Bomber Fans	Mark C Webb	N Callow
7x722	Embroidery	Darren Weir	B Allen
9x4	Hold My Helmet	L & T Corstens	J Neil
Kastor	Rachael Cunningham	J Bates
8x87	Milalba	Doug Harrison	J Bayliss
	Miss United States	Enver Jusufovic	W Egan
Xin Loi	M Beer & M Hinto...	A Mallyon
Cranbourne Race # 3 - Friday, 10 March 2017 7:30pm		P
Form Guide: Ferntree Gully Nissan Maiden - 1400m
Last 10	Horse	Trainer	Jockey	Bar	Prize	Final Rating
366243x	Nangawooka	Matthew Brown	J Mott
x950x06597	Out Of The Red	Mario Farrugia	B Stockdale
Panalley	Gary McGreal	B Werner
907x	Street Crystal	Mark C Webb	A Lynch
9x060x9803	Trappy's Boy	Daniel Daniel	C Symons
Supergrass	Michael Kent	L Nolen
26825528x7	Barley Mo	Greg Hayes	E Brown
Meowie Wowie	Rob Blacker	D Moor
240x583	Worship The Blade	Henry Dwyer	J Noonan
544x50	Hidden Away	D & B Hayes &amp...	A Mallyon
25x87x	Shenandoah	T Busuttin & N Y...	J Bayliss

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:364px;"><col style="width:130px;"><col style="width:84px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:78px;"><col style="width:93px;"><col style="width:94px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: center"]Odds[/TD]

[TD="align: center"]finished place[/TD]
[TD="align: center"]highest rated[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]5825[/TD]
[TD="align: center"]51.00[/TD]
[TD="align: right"]-5.7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]15915[/TD]
[TD="align: center"]1.70[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99cc00]#99cc00[/URL] , align: right"]23.2[/TD]
[TD="align: center"]1st[/TD]
[TD="align: center"]Max1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]3775[/TD]
[TD="align: center"]31.00[/TD]
[TD="align: right"]-4.4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]75743[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]3825[/TD]
[TD="align: center"]6.50[/TD]
[TD="align: right"]19.3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]3675[/TD]
[TD="align: center"]19.00[/TD]
[TD="align: right"]18.6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]6925[/TD]
[TD="align: center"]3.10[/TD]
[TD="align: right"]10.1[/TD]
[TD="align: center"]2nd[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2725[/TD]
[TD="align: center"]10.00[/TD]
[TD="align: right"]8.7[/TD]
[TD="align: center"]3rd[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]

[TD="align: center"]Odds[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]4300[/TD]
[TD="align: center"]61.00[/TD]
[TD="align: right"]-3.1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]6630[/TD]
[TD="align: center"]23.00[/TD]
[TD="align: right"]0.5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]1125[/TD]
[TD="align: center"]81.00[/TD]
[TD="align: right"]3.1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]26.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]4750[/TD]
[TD="align: center"]13.00[/TD]
[TD="align: right"]4.2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]10810[/TD]
[TD="align: center"]1.75[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99cc00]#99cc00[/URL] , align: right"]30.3[/TD]
[TD="align: center"]1st[/TD]
[TD="align: center"]Max2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]21[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1475[/TD]
[TD="align: center"]5.00[/TD]
[TD="align: right"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]22[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]1600[/TD]
[TD="align: center"]6.00[/TD]
[TD="align: right"]20.3[/TD]
[TD="align: center"]2nd[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]23[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]1700[/TD]
[TD="align: center"]14.00[/TD]
[TD="align: right"]-52.9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]24[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]16.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]25[/TD]
[TD="align: right"]767[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1575[/TD]
[TD="align: center"]26.00[/TD]
[TD="align: right"]5.5[/TD]
[TD="align: center"]3rd[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]26[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]27[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]28[/TD]

[TD="align: center"]Odds[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]29[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]10435[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99cc00]#99cc00[/URL] , align: right"]27.8[/TD]
[TD="align: center"]3rd[/TD]
[TD="align: center"]Max3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]30[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]5840[/TD]

[TD="align: right"]-5.2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]31[/TD]
[TD="align: right"]9538[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]3200[/TD]

[TD="align: right"]9.1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]32[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]1150[/TD]

[TD="align: right"]-24.6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]33[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]7870[/TD]

[TD="align: right"]14.5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]34[/TD]
[TD="align: right"]33[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]3760[/TD]

[TD="align: right"]25.7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]35[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]21400[/TD]

[TD="align: right"]10.6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]36[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]1125[/TD]

[TD="align: right"]9.8[/TD]
[TD="align: center"]2nd[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]37[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]7600[/TD]

[TD="align: right"]20.9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]38[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]3180[/TD]

[TD="align: right"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]39[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]7533[/TD]

[TD="align: right"]8.9[/TD]
[TD="align: center"]1st[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula J5 =IF(H5=MAX(INDEX(H$1:H4,AGGREGATE(14,6,ROW(D$1:D4)/(D$1:D4=""),1)):INDEX(H6:H35,MATCH(TRUE,INDEX(D6:D35="",0),0))),"Max","") J6 =IF(H6=MAX(INDEX(H$1:H5,AGGREGATE(14,6,ROW(D$1:D5)/(D$1:D5=""),1)):INDEX(H7:H36,MATCH(TRUE,INDEX(D7:D36="",0),0))),"Max1","") J7 =IF(H7=MAX(INDEX(H$1:H6,AGGREGATE(14,6,ROW(D$1:D6)/(D$1:D6=""),1)):INDEX(H8:H37,MATCH(TRUE,INDEX(D8:D37="",0),0))),"Max","") J8 =IF(H8=MAX(INDEX(H$1:H7,AGGREGATE(14,6,ROW(D$1:D7)/(D$1:D7=""),1)):INDEX(H9:H38,MATCH(TRUE,INDEX(D9:D38="",0),0))),"Max","") J9 =IF(H9=MAX(INDEX(H$1:H8,AGGREGATE(14,6,ROW(D$1:D8)/(D$1:D8=""),1)):INDEX(H10:H39,MATCH(TRUE,INDEX(D10:D39="",0),0))),"Max","") J10 =IF(H10=MAX(INDEX(H$1:H9,AGGREGATE(14,6,ROW(D$1:D9)/(D$1:D9=""),1)):INDEX(H11:H40,MATCH(TRUE,INDEX(D11:D40="",0),0))),"Max","") J11 =IF(H11=MAX(INDEX(H$1:H10,AGGREGATE(14,6,ROW(D$1:D10)/(D$1:D10=""),1)):INDEX(H12:H41,MATCH(TRUE,INDEX(D12:D41="",0),0))),"Max","") J12 =IF(H12=MAX(INDEX(H$1:H11,AGGREGATE(14,6,ROW(D$1:D11)/(D$1:D11=""),1)):INDEX(H13:H42,MATCH(TRUE,INDEX(D13:D42="",0),0))),"Max","") J13 =IF(H13=MAX(INDEX(H$1:H12,AGGREGATE(14,6,ROW(D$1:D12)/(D$1:D12=""),1)):INDEX(H14:H43,MATCH(TRUE,INDEX(D14:D43="",0),0))),"Max","") J14 =IF(H14=MAX(INDEX(H$1:H13,AGGREGATE(14,6,ROW(D$1:D13)/(D$1:D13=""),1)):INDEX(H15:H44,MATCH(TRUE,INDEX(D15:D44="",0),0))),"Max","") J15 =IF(H15=MAX(INDEX(H$1:H14,AGGREGATE(14,6,ROW(D$1:D14)/(D$1:D14=""),1)):INDEX(H16:H45,MATCH(TRUE,INDEX(D16:D45="",0),0))),"Max","") J16 =IF(H16=MAX(INDEX(H$1:H15,AGGREGATE(14,6,ROW(D$1:D15)/(D$1:D15=""),1)):INDEX(H17:H46,MATCH(TRUE,INDEX(D17:D46="",0),0))),"Max","") J17 =IF(H17=MAX(INDEX(H$1:H16,AGGREGATE(14,6,ROW(D$1:D16)/(D$1:D16=""),1)):INDEX(H18:H47,MATCH(TRUE,INDEX(D18:D47="",0),0))),"Max","") J18 =IF(H18=MAX(INDEX(H$1:H17,AGGREGATE(14,6,ROW(D$1:D17)/(D$1:D17=""),1)):INDEX(H19:H48,MATCH(TRUE,INDEX(D19:D48="",0),0))),"Max","") J19 =IF(H19=MAX(INDEX(H$1:H18,AGGREGATE(14,6,ROW(D$1:D18)/(D$1:D18=""),1)):INDEX(H20:H49,MATCH(TRUE,INDEX(D20:D49="",0),0))),"Max","") J20 =IF(H20=MAX(INDEX(H$1:H19,AGGREGATE(14,6,ROW(D$1:D19)/(D$1:D19=""),1)):INDEX(H21:H50,MATCH(TRUE,INDEX(D21:D50="",0),0))),"Max2","") J21 =IF(H21=MAX(INDEX(H$1:H20,AGGREGATE(14,6,ROW(D$1:D20)/(D$1:D20=""),1)):INDEX(H22:H51,MATCH(TRUE,INDEX(D22:D51="",0),0))),"Max","") J22 =IF(H22=MAX(INDEX(H$1:H21,AGGREGATE(14,6,ROW(D$1:D21)/(D$1:D21=""),1)):INDEX(H23:H52,MATCH(TRUE,INDEX(D23:D52="",0),0))),"Max","") J23 =IF(H23=MAX(INDEX(H$1:H22,AGGREGATE(14,6,ROW(D$1:D22)/(D$1:D22=""),1)):INDEX(H24:H53,MATCH(TRUE,INDEX(D24:D53="",0),0))),"Max","") J24 =IF(H24=MAX(INDEX(H$1:H23,AGGREGATE(14,6,ROW(D$1:D23)/(D$1:D23=""),1)):INDEX(H25:H54,MATCH(TRUE,INDEX(D25:D54="",0),0))),"Max","") J25 =IF(H25=MAX(INDEX(H$1:H24,AGGREGATE(14,6,ROW(D$1:D24)/(D$1:D24=""),1)):INDEX(H26:H55,MATCH(TRUE,INDEX(D26:D55="",0),0))),"Max","") J26 =IF(H26=MAX(INDEX(H$1:H25,AGGREGATE(14,6,ROW(D$1:D25)/(D$1:D25=""),1)):INDEX(H27:H56,MATCH(TRUE,INDEX(D27:D56="",0),0))),"Max","") J27 =IF(H27=MAX(INDEX(H$1:H26,AGGREGATE(14,6,ROW(D$1:D26)/(D$1:D26=""),1)):INDEX(H28:H57,MATCH(TRUE,INDEX(D28:D57="",0),0))),"Max","") J28 =IF(H28=MAX(INDEX(H$1:H27,AGGREGATE(14,6,ROW(D$1:D27)/(D$1:D27=""),1)):INDEX(H29:H58,MATCH(TRUE,INDEX(D29:D58="",0),0))),"Max","") J29 =IF(H29=MAX(INDEX(H$1:H28,AGGREGATE(14,6,ROW(D$1:D28)/(D$1:D28=""),1)):INDEX(H30:H59,MATCH(TRUE,INDEX(D30:D59="",0),0))),"Max3","") J30 =IF(H30=MAX(INDEX(H$1:H29,AGGREGATE(14,6,ROW(D$1:D29)/(D$1:D29=""),1)):INDEX(H31:H60,MATCH(TRUE,INDEX(D31:D60="",0),0))),"Max","") J31 =IF(H31=MAX(INDEX(H$1:H30,AGGREGATE(14,6,ROW(D$1:D30)/(D$1:D30=""),1)):INDEX(H32:H61,MATCH(TRUE,INDEX(D32:D61="",0),0))),"Max","") J32 =IF(H32=MAX(INDEX(H$1:H31,AGGREGATE(14,6,ROW(D$1:D31)/(D$1:D31=""),1)):INDEX(H33:H62,MATCH(TRUE,INDEX(D33:D62="",0),0))),"Max","") J33 =IF(H33=MAX(INDEX(H$1:H32,AGGREGATE(14,6,ROW(D$1:D32)/(D$1:D32=""),1)):INDEX(H34:H63,MATCH(TRUE,INDEX(D34:D63="",0),0))),"Max","") J34 =IF(H34=MAX(INDEX(H$1:H33,AGGREGATE(14,6,ROW(D$1:D33)/(D$1:D33=""),1)):INDEX(H35:H64,MATCH(TRUE,INDEX(D35:D64="",0),0))),"Max","") J35 =IF(H35=MAX(INDEX(H$1:H34,AGGREGATE(14,6,ROW(D$1:D34)/(D$1:D34=""),1)):INDEX(H36:H65,MATCH(TRUE,INDEX(D36:D65="",0),0))),"Max","") J36 =IF(H36=MAX(INDEX(H$1:H35,AGGREGATE(14,6,ROW(D$1:D35)/(D$1:D35=""),1)):INDEX(H37:H66,MATCH(TRUE,INDEX(D37:D66="",0),0))),"Max","") J37 =IF(H37=MAX(INDEX(H$1:H36,AGGREGATE(14,6,ROW(D$1:D36)/(D$1:D36=""),1)):INDEX(H38:H67,MATCH(TRUE,INDEX(D38:D67="",0),0))),"Max","") J38 =IF(H38=MAX(INDEX(H$1:H37,AGGREGATE(14,6,ROW(D$1:D37)/(D$1:D37=""),1)):INDEX(H39:H68,MATCH(TRUE,INDEX(D39:D68="",0),0))),"Max","") J39 =IF(H39=MAX(INDEX(H$1:H38,AGGREGATE(14,6,ROW(D$1:D38)/(D$1:D38=""),1)):INDEX(H40:H69,MATCH(TRUE,INDEX(D40:D69="",0),0))),"Max","") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4
this is what it should look like
have also put a new file there soo you can see where it is going wrong(there is more data there just ignore that)
thanks richard
Book3 - Jumpshare

 

[Peter_SSs]

Re: top rated formula help

Good to see that you have got Excel jeanie working reasonably well. Two further tips though:
1. You can use the ‘Analyse range (Forum)’ field near the top left of the Excel jeanie screen to restrict the number of formulas generated. In that field, you can use Ctrl+Click/Drag to select multiple disjoint ranges if required. There is generally no need to display multiple formulas that are basically the same, it just fills up the board and makes your post and the thread hard to read/navigate.
2. If you tick 'Gridlines' and 'Borders' the images should look better still.


To add the numbers to "Max", just add this blue text into the existing J5 formula near the end and copy down.

,"Max"&COUNTIF(J$4:J4,"?*"),"")

 

[Peter_SSs]

Re: top rated formula help

have also put a new file there soo you can see where it is going wrong

Looking at Sheet2 of that file ..
If you select any cell in the range S1:S40 and look on the Home ribbon tab -> Number group you should see "Number" as the visible item in the drop-down box, indicating that the selected cell is formatted as a number.
If you select any cell in column S below that, the drop-down shows Text indicating that the cell contains text, not a number and that is causing the problem.

A solution is .
- Select the whole of column S by clicking its heading label
- Data ribbon tab -> Text to Columns -> Delimited -> Next -> Remove any ticks from all 5 Delimiters boxes -> Finish.

That should convert all the "Numbers stored as text" into "proper" numbers & the formula results should come good.

 

[thebeans]

Re: top rated formula help

it works up too max15 on the sheet then goes all wrong for some reason
Book3 - Jumpshare

have just worked out that if i input the value in column S (the top rated)it come up good if that helps

 

[Peter_SSs]

Re: top rated formula help

This is getting quite confusing as the columns keep changing.

1. The formula change I gave you in post 14 was in response to your formula in post 13 that was in column J. In the file you have just provided, the formula is now in column X but the COUNTIF still refers to column J, not column X.

2. In post 13 the 'max' formula was in column J and that column had a heading ("highest rated") in row 5 & I made my suggestion based on that heading being there. Your latest file does not have a heading in row 5 of column X.

3. In your latest file, you still have some numbers stored as text in the 'Final Rating' column (S). For example, in cell Z160, put this formula =ISNUMBER(S160) then in cell Z169 put this formula =ISNUMBER(S169) and look at the two results.

4. I notice that your formula is still using the Jockey column (D) which is sometimes blank, causing possibly incorrect results. Why not change, as suggested to a column that does not have those blanks? From what I can see in the sample file, column D does not have any such blanks except between races which is what we want.

 

[thebeans]

sorry peter for all the confusion.it is working good now soo thank you very much for you help
richard

 

[Peter_SSs]

sorry peter for all the confusion.it is working good now soo thank you very much for you help
richard

You are welcome. Glad you got it sorted out.