Eliminating #N/A when duplicate scores

[Johnmus]

Hi everyone,

I am using the following array formula to determine the score and name of school

=IF(AZ125="","",(FIXED(AZ125,1)&"   "&INDEX($B$4:$B$123,MATCH(COUNT(AZ$125:AZ125),$AX$4:$AX$123,0))))

This works great except when there is a tie (I am using a tie-breaker in the rank formula) in the tie breaker and the score ends up to be the same, then I will get the following:

Score (Column AZ) Score and School Column BA
91.30 91.3 School 1
91.30 91.3 #N/A

Is there a way to list the second school with the same score?

Thanks for any help!

 

[Aladin Akyurek]

Hi everyone,

I am using the following array formula to determine the score and name of school

=IF(AZ125="","",(FIXED(AZ125,1)&"   "&INDEX($B$4:$B$123,MATCH(COUNT(AZ$125:AZ125),$AX$4:$AX$123,0))))

This works great except when there is a tie (I am using a tie-breaker in the rank formula) in the tie breaker and the score ends up to be the same, then I will get the following:

Score (Column AZ) Score and School Column BA
91.30 91.3 School 1
91.30 91.3 #N/A

Is there a way to list the second school with the same score?

Thanks for any help!

An easier to manage approach would probably suit better...

Would you post a small sample along with the criteria you need to use?

 

[Johnmus]

Hi,

I will try. I cannot post an attachment yet.

I am creating competition score sheet with different classifications - there could be up to 12 schools in each class:

Column A (Class) Column B (School Name) Column C, D, E,F (Scores) Column H (Total Score) Column J (Place based on ranking also inlcudes a tiebreaker on column D)
Here is the ranking formula:

=IF(H4="","",COUNTIFS(H$4:H$29,">"&H4)+1+COUNTIFS(H$4:H$29,H4,D$4:D$29,">"&D4)-SUMPRODUCT((H$4:H$29&" "&B$4:B$29=H$30)+0))

Since I want to eliminate the highest score overall of all classes there is a formula in H29 for the sweepstakes winner

Now I need to list the placements and score from 1st through 12 in each class:
On the bottom of the sheet I typed a list from 1st place through 12th place (K31:K43) and next to it in Column L use the following array formulu to rank the scores from 1 throuogh 12 - this also leaves out the overall high score (H29):

=IF(K31="","",LARGE(IF(ISNUMBER(1/$J$4:$J$29),$H$4:$H$29),(COUNTA($K$31:K31))))

Finally I want to list the score and school in one cell to transfer to an awards sheet for the announcer. Here is that formula in Column M:

=IF(L31="","",(FIXED(L31,1)&"   "&INDEX($B$4:$B$29,MATCH(COUNT(L$31:L31),$J$4:$J$29,0))))

When the scores are tied it will list the duplicate scores next to 1st Place and 2nd Place for example but the score- school formula results in school name of the first place and #N/A for the school that ties.

I am sorry for the lengthy explanation. Hope this is clear. Probaly would be better if I could attach a sample of the workbook.

Appreciate you looking at this.

Column K Column L Column M
<TABLE style="WIDTH: 234pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=312><COLGROUP><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1938" width=53><COL style="WIDTH: 125pt; mso-width-source: userset; mso-width-alt: 6107" width=167><TBODY><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 69pt; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl230 height=24 width=92>1st Place</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 40pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl231 width=53>89.0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 125pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl232 width=167>89.0 School 1</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl230 height=24>2nd Place</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl231>89.0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl232 align=middle>#N/A</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl230 height=24>3rd Place</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl231>88.5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl232>88.5 School 2</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl230 height=24>4th Place</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl231>88.5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl232>88.5 School 8</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl230 height=24>5th Place</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl231>88.0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl232>88.0 School 9</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl230 height=24>6th Place</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl231>88.0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl232 align=middle>#N/A</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl230 height=24>7th Place</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl231>66.5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl232>66.5 School 5</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl230 height=24>8th Place</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl231>66.0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl232>66.0 School 6</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl230 height=24>9th Place</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl231>55.0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl232>55.0 School 7</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl230 height=24>10th Place</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl231>0.0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl232>0.0 0</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl230 height=24>11th Place</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl231>0.0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl232 align=middle>#N/A</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl230 height=24>12th Place</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl231>0.0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl232 align=middle>#N/A</TD></TR></TBODY></TABLE>

 

[Johnmus]

Here is a sample. Sorry it took so long.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	CLASS	Parade	Judge				TOTAL SCORE	FINAL SCORE	PLACE	PLACE
2			Vocabulary (Content)	Excellence (Execution)	Showmanship	Marching Excellence
3
4	MSA	School 1	44	43	47	44	178	89.0	2	2
5	MSA	School 2	44	45	44	44	177	88.5	4	4
6	MSA	School 3	45	44	44	55	188	94.0	1	1
7	MSA	School 4	44	43	46	45	178	89.0	2	2
8	MSA	School 5	38	38	28	29	133	66.5	11	11
9	MSA	School 6	33	33	33	33	132	66.0	12	12
10	MSA	School 7	44	22	22	22	110	55.0	13	13
16	2A	School 8	33	44	56	44	177	88.5	5	5
17	2A	School 9	44	44	44	44	176	88.0	6	6
18	2A	School 10	44	44	44	44	176	88.0	6	6
19	2A	School 11	32	44	43	44	163	81.5	9	9
20	2A	School 12	34	45	45	44	168	84.0	8	8
21	2A	School 13	54	51	44	44	193	96.5	SS	0
22	2A	School 14	34	32	39	29	134	67.0	10	10
30								96.5 School 13	Sweepstakes
31											1st Place	94.0	94.0 School 3
32											2nd Place	89.0	89.0 School 1
33											3rd Place	89.0	#N/A
34											4th Place	88.5	88.5 School 2
35											5th Place	88.5	88.5 School 8
36											6th Place	88.0	88.0 School 9
37											7th Place	88.0	#N/A
38											8th Place	84.0	84.0 School 12
39											9th Place	81.5	81.5 School 11
40											10th Place	67.0	67.0 School 14
41											11th Place	66.5	66.5 School 5
42											12th Place	66.0	66.0 School 6
???

Excel 2007