Rank, dynamic names, equal ranking

[JimP]

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 52px"><COL style="WIDTH: 208px"><COL style="WIDTH: 89px"><COL style="WIDTH: 61px"><COL style="WIDTH: 54px"><COL style="WIDTH: 65px"><COL style="WIDTH: 32px"><COL style="WIDTH: 38px"><COL style="WIDTH: 37px"><COL style="WIDTH: 29px"><COL style="WIDTH: 29px"><COL style="WIDTH: 29px"><COL style="WIDTH: 29px"><COL style="WIDTH: 29px"><COL style="WIDTH: 49px"><COL style="WIDTH: 70px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left">CLASS</TD><TD style="FONT-WEIGHT: bold">ENTRY</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Place in Class</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Place in Class</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">Overall Place</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"># of Judges</TD><TD style="FONT-WEIGHT: bold">J1 JW</TD><TD style="FONT-WEIGHT: bold">J2 SBW</TD><TD style="FONT-WEIGHT: bold">J3 MCL</TD><TD style="FONT-WEIGHT: bold">J4 KG</TD><TD style="FONT-WEIGHT: bold">J5 RP</TD><TD style="FONT-WEIGHT: bold">J6 JP</TD><TD style="FONT-WEIGHT: bold">J7 PJ</TD><TD style="FONT-WEIGHT: bold">J8 RB</TD><TD style="FONT-WEIGHT: bold">Total Marks</TD><TD style="FONT-WEIGHT: bold">Average Marks</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: left">1</TD><TD>7034 - One Summer Holiday</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">#REF!</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">205</TD><TD style="TEXT-ALIGN: center">290</TD><TD style="TEXT-ALIGN: center">250</TD><TD style="TEXT-ALIGN: center">130</TD><TD style="TEXT-ALIGN: center">215</TD><TD style="TEXT-ALIGN: center">210</TD><TD style="TEXT-ALIGN: center">230</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: center">1,530</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">219</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: left">1</TD><TD>7008 - Scenes from the BBC</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">16</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">180</TD><TD style="TEXT-ALIGN: center">170</TD><TD style="TEXT-ALIGN: center">280</TD><TD style="TEXT-ALIGN: center">150</TD><TD style="TEXT-ALIGN: center">225</TD><TD style="TEXT-ALIGN: center">155</TD><TD style="TEXT-ALIGN: center">220</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: center">1,380</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">197</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D3</TD><TD>=RANK(P3,INDIRECT(CONCATENATE("Class",A3)))</TD></TR><TR><TD>E3</TD><TD>=IF(ISERROR(RANK(P3,$P$3:$P$40)),"",RANK(P3,$P$3:$P$40))</TD></TR><TR><TD>O3</TD><TD>=SUM(G3:N3)</TD></TR><TR><TD>P3</TD><TD>{=IF(ISERROR(ROUND(AVERAGE(IF(G3:N3<>0, G3:N3,"")),0)),"",ROUND(AVERAGE(IF(G3:N3<>0, G3:N3,"")),0))}</TD></TR><TR><TD>D4</TD><TD>=RANK(P4,Class1)</TD></TR><TR><TD>E4</TD><TD>=IF(ISERROR(RANK(P4,$P$3:$P$40)),"",RANK(P4,$P$3:$P$40))</TD></TR><TR><TD>O4</TD><TD>=SUM(G4:N4)</TD></TR><TR><TD>P4</TD><TD>{=IF(ISERROR(ROUND(AVERAGE(IF(G4:N4<>0, G4:N4,"")),0)),"",ROUND(AVERAGE(IF(G4:N4<>0, G4:N4,"")),0))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

</TD></TR></TBODY></TABLE>

Excel tables to the web >> Excel Jeanie HTML 4
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Dynamic Ranking<o></o>
I’m attempting to produce a spreadsheet template to record and rank the entrants to a competition which will adapt to varying numbers of finalists in numerous classes (others not shown for clarity) in future years. I’ve managed to make most of it dynamic to cope with this but there are two bits I cannot sort out. <o></o>
<o></o>
<o></o>
Named dynamic range Class 1<o></o>
=OFFSET(INDIRECT("$A$"&MATCH(1,$A:$A,0)),0,MATCH("Average Marks",$1:$1,0)-1,COUNTIF($A$2:$A$50,"1"),1)<o></o>
<o></o>
<o>Dynamic Ranking<o></o>
</o>If I use the formula in the form shown in D4 it ranks within each class. If I try to make the formula more “dynamic” by trying to create the 2<SUP>nd</SUP> rank parameter from the word “Class” and the class number found in A3, A4 etc I get errors. I’ve tried various formulas with no success so any suggestions would be gratefully received.<o></o>
<o></o>
Equal Ranking<o></o>
Something else I would like to do is display equal ranking with a trailing equal sign, but I’ve really no idea how to go about it although I did try <o></o>
IF(COUNTIF(RANK(Q25,class8),">1"),RANK(Q25,class8),RANK(Q25,class8)&"=") but without success. Again any help would be appreciated.<o></o>
<o></o>
This is my 1<SUP>st</SUP> post and use of Excel Jeanie so if I have omitted anything please bear with me. I have searched the forum but could see no posts that covered these particular queries.<o></o>
<o></o>
Many thanks.<o></o>
<o></o>
Jim<o></o>
<o></o>
<o></o>

 

[JimP]

Can anyone help with my questions as I would like to use this spreadsheet on Saturday?!?

If there is any other information I need to supply that will assist potential helpers please let me know.

Many thanks in advance.

Jim