Help with formula to assign points based on rank

[stephenday]

Hi,

I'm hoping someone can assist with a formula that I am really struggling to work out.

I need to assign scores to an individual based on their rank each week. Rank is determined by a number of wins from 1 - 4 (or 5 if a tiebreak is required). There will be tiebreakers if multiple people are on the same number of points which ensure there will always be a differing number of wins per each individual. This should hopefully make the formula simpler, but the formula may need to take into account the tiebreaker games also, but this is surely just a case of including more cells where necessary.

Points will be assigned in the following order each week:

1st Place - 4 Points
2nd Place - 3 Points
3rd Place - 2 Points
4th Place - 1 Point
5th Place - 0 Points

The formula therefore needs to sum up the number of wins per week, rank these in order from 1st to last (or vice versa if necessary) and then assign either 4, 3,2, 1 or 0 points based on that ranking.

One problem is that the list of scores is not in a sequential list, so from the research I have done, I do not believe the rank function will work correctly. The scores are in cells horizontally with gaps in between (i.e. cells a1, c1, e1, g1 etc).

I would greatly appreciate any assistance you can provide in helping me solve this.

Thanks,

Steve

 

[stephenday]

Scores 1

*	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Results	Player 1	*	Player 2	*	Player 3	*	Player 4	*	Player 5	*
3	Week	Game No.	*	*	*	*	*	*	*	*	*	*
4	1	1	1	0	WIN	0	0	LOSE	0	0	LOSE	0	0	LOSE	0	0	LOSE
5	1	2	2	0	WIN	0	0	LOSE	0	0	LOSE	0	0	LOSE	0	0	LOSE
6	1	3	3	0	WIN	0	0	LOSE	0	0	LOSE	0	0	LOSE	0	0	LOSE
7	1	4	4	0	WIN	0	0	LOSE	0	0	LOSE	0	0	LOSE	0	0	LOSE
8	1	Tiebreak	0	0	LOSE	0	0	LOSE	0	0	LOSE	0	0	LOSE	0	0	LOSE
9	1	Points	4	3	2	1	0

<tbody>
</tbody>

To assist, a copy of the document I am working on is shown above. The cells I am trying to fill with the formula are those with the red text. The formula needs to take the win/lose cells (which can be amended to another format if it makes the formula easier), and then the points assigned based on the number of wins / lose.

Thanks,

Stephen