Formula Help!!

[Tom and Rons Pools]

I have set up a ranking system for my category and then used a formula to assign each a point total. However the points are coming in reverse order.

Here is my spreadsheet and I will show the formulas below:

Team	Goals	Rank	Roto
1​	13​	3​	3.5​
2​	13​	3​	3.5​
3​	22​	1​	1​
4​	14​	2​	2​
5​	11​	5​	5​
6​	8​	7​	7​
7​	9​	6​	6​
8​	3​	10​	10.5​
9​	4​	9​	9​
10​	5​	8​	8​
11​	3​	10​	10.5​
12​	1​	12​	12​

The rank is correct and here is the formula I used: =RANK(B2,$B$2:$B$13)

Here is the formula under root column but the points are assigning in the wrong order: =IF(COUNTIF($C$2:$C$13,C2)>1,((C2-1)+(C2+(COUNTIF($C$2:$C$13,C2))))/2,C2)

The points should be the person who has the most goals gets the 12 and so on.

 

[alz]

You can directly set the point by rank using third argument!
=RANK.AVG(B2,$B$2:$B$13,1)

 

[Fluff]

Maybe

+Fluff 1.xlsm
	A	B	C	D
1	Team	Goals	Rank	Roto
2	1	13	3	10.5
3	2	13	3	10.5
4	3	22	1	12
5	4	14	2	11
6	5	11	5	8
7	6	8	7	6
8	7	9	6	7
9	8	3	10	3.5
10	9	4	9	4
11	10	5	8	5
12	11	3	10	3.5
13	12	1	12	1
Main

Cell Formulas
Range	Formula
C2:C13	C2	=RANK(B2,$B$2:$B$13)
D2:D13	D2	=IF(COUNTIF($C$2:$C$13,C2)>1,13-C2+1/COUNTIF($C$2:$C$13,C2),13-C2)

Although I don't understand why the 2 teams ranked 3rd woulf get 10.5 points rather than 9.5

 

[Tom and Rons Pools]

Maybe

+Fluff 1.xlsm
	A	B	C	D
1	Team	Goals	Rank	Roto
2	1	13	3	10.5
3	2	13	3	10.5
4	3	22	1	12
5	4	14	2	11
6	5	11	5	8
7	6	8	7	6
8	7	9	6	7
9	8	3	10	3.5
10	9	4	9	4
11	10	5	8	5
12	11	3	10	3.5
13	12	1	12	1
Main

Cell Formulas
Range	Formula
C2:C13	C2	=RANK(B2,$B$2:$B$13)
D2:D13	D2	=IF(COUNTIF($C$2:$C$13,C2)>1,13-C2+1/COUNTIF($C$2:$C$13,C2),13-C2)

Although I don't understand why the 2 teams ranked 3rd woulf get 10.5 points rather than 9.5

Thank you. This worked great!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Tom and Rons Pools]

Maybe

+Fluff 1.xlsm
	A	B	C	D
1	Team	Goals	Rank	Roto
2	1	13	3	10.5
3	2	13	3	10.5
4	3	22	1	12
5	4	14	2	11
6	5	11	5	8
7	6	8	7	6
8	7	9	6	7
9	8	3	10	3.5
10	9	4	9	4
11	10	5	8	5
12	11	3	10	3.5
13	12	1	12	1
Main

Cell Formulas
Range	Formula
C2:C13	C2	=RANK(B2,$B$2:$B$13)
D2:D13	D2	=IF(COUNTIF($C$2:$C$13,C2)>1,13-C2+1/COUNTIF($C$2:$C$13,C2),13-C2)

Although I don't understand why the 2 teams ranked 3rd woulf get 10.5 points rather than 9.5

I just realized you are actually right. It should be 9.5 and not 10.5. Now I'm not sure how to handle.

 

[Fluff]

In that case how about

+Fluff 1.xlsm
	A	B	C	D
1	Team	Goals	Rank	Roto
2	1	13	3	9.5
3	2	13	3	9.5
4	3	22	1	12
5	4	14	2	11
6	5	11	5	8
7	6	8	7	6
8	7	9	6	7
9	8	3	10	2.5
10	9	4	9	4
11	10	5	8	5
12	11	3	10	2.5
13	12	1	12	1
Summary

Cell Formulas
Range	Formula
C2:C13	C2	=RANK(B2,$B$2:$B$13)
D2:D13	D2	=13-RANK.AVG(C2,$C$2:$C$13,1)