Need help ASAP

[adurham22926192]

I’m making an Irish Dance scoring system and I need help. I have Column A for the competitors numbers (1,2,3 etc) then Column B is their first mark they got (78,73,79 etc) Column C is the Second mark they got (73,75,78 etc) and Column D is the total of Column B and Column C for each dancer (151 etc). I then want Column I to be the place (1st, 2nd, 3rd etc) and if ties between two dancers say who are in second I want it to say 2nd 2nd and then under it still say 3rd. Column J I want whoever is 1st their number to come up and Column K I want their total marks to come up. Column M I want converted marks that are already entered in another sheet to come up ( 1st = 100 marks 2nd = 49 3rd = 24 4th = 11 5th = 6 6th = 5 7th = 4 8th = 3 9th = 2 and 10th place = 1. But if two people have the same total let’s say 176 and they are in second place because the first place person has 178 points, I want the first place person to get 100 but the second and 3rd place marks to be added together and divided by how many people got second in this case 2 competitors. So it would be 49 (2nd place mark) + 24 (3rd place mark) = 73 marks divided by 2 competitors = 36.5 points so the two people in second place would get 36.5 points each. I hope this makes sense I need so much help!! Thanks

 

[Eric W]

This seems to be what you describe:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Competitor	Mark 1	Mark 2	Total Marks					Place	Competitors by rank	Total Marks		Converted Marks		Converted Marks Table
2	1	78	73	151					4	12	159		100		1	100
3	2	73	75	148					5	3	157		36.5		2	49
4	3	79	78	157					2	9	157		36.5		3	24
5	4	82	69	151					4	5	154		11		4	11
6	5	77	77	154					3	1	151		5.5		5	6
7	6	56	78	134					8	4	151		5.5		6	5
8	7	65	77	142					6	2	148		4		7	4
9	8	44	69	113					9	7	142		3		8	3
10	9	76	81	157					2	10	135		2		9	2
11	10	55	80	135					7	6	134		1		10	1
12	11	39	62	101					10	8	113		0
13	12	84	75	159					1	11	101		0
Sheet3

Cell Formulas
Range	Formula
I2:I13	I2	{=SUM(--(D$2:D$13>D2))-SUM(IFERROR(EXP(LN(FREQUENCY(IF(D$2:D$13>D2,D$2:D$13),D$2:D$13)-1)),0))+1}
J2:J13	J2	{=INDEX(A$2:A$13,MATCH(SMALL(I$2:I$13,ROWS(J$2:J2)),IF(COUNTIF(J$1:J1,A$2:A$13)>0,-1,I$2:I$13),0))}
K2:K13	K2	=INDEX(D$2:D$13,MATCH(J2,A$2:A$13,0))
D2:D13	D2	=SUM(B2:C2)
M2:M13	M2	=SUM(OFFSET(P$2,COUNTIF(K$2:K$13,">"&K2),0,COUNTIF(K$2:K$13,K2)))/COUNTIF(K$2:K$13,K2)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

However, I strongly recommend some changes. First, columns I and J are adjacent and easily confused. I'd add another column with the numbers 1-10 between them with the positions.

Second, the formula in I is pretty complicated (maybe someone can improve on it). But worse than that, it's confusing. If you have 2 people tied for second, the next person should be 4th, not third, because fourth place has 3 people ahead of it. Moreover, look at competitor 6. In this scenario, competitor 6 came in eighth place. So in looking where competitor 6 falls in column J, they get 1 converted mark in column M. But according to the table, eighth place should get 3 converted marks. This is due to the ties ahead of that person. But if you say competitor 6 really is in 10th place, so should get 1 point, you can look directly at the table instead of having to figure out how many ties are ahead.

In any case, take a look and see if this is close to what you want.

 

[mrshl9898]

Needed some helper columns, and assuming your data starts in row 2.

First sort descending by column D then:

I2 (copied down)= =SUM(P$2:P2)-SUM(Q$2:Q2)&CHOOSE(AND(SUM(P$2:P2)-SUM(Q$2:Q2)<>{11,12,13})*MIN(4,MOD(SUM(P$2:P2)-SUM(Q$2:Q2),10))+1,"th","st","nd","rd","th")
J1 = =A1
K2 = =D1
L2 (copied down)= =IF(I2=I1,SUM(R1:R2)/2,IF(I2=I3,SUM(R2:R3)/2,R2))
P2 (copied down)= 1
Q2 (copied down)= =IF(COUNTIF(D$2:D2,D2)>1,1,0)
R2 (copied down)= 100, R3 = 49 etc...

 

[Peter_SSs]

This is a continuation of: Need help ASAP

Please do not post the same question multiple times. Questions of a duplicate nature will be locked or deleted, per #12 of the Forum Rules and points 6 & 7 of the Guidelines.

Any bumps, clarifications, or follow-ups should be posted to the linked thread.