Help with formula to assign points based on rank

stephenday

New Member
Joined
May 31, 2017
Messages
2
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Scores 1

*ABCDEFGHIJKLMNOPQ
WINLOSELOSELOSELOSE
WINLOSELOSELOSELOSE
WINLOSELOSELOSELOSE
WINLOSELOSELOSELOSE
LOSELOSELOSELOSELOSE

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #333399, colspan: 2, align: center"]Results[/TD]
[TD="bgcolor: #333399, colspan: 2, align: center"]Player 1[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399, colspan: 2, align: center"]Player 2[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399, colspan: 2, align: center"]Player 3[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399, colspan: 2, align: center"]Player 4[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399, colspan: 2, align: center"]Player 5[/TD]
[TD="bgcolor: #333399"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #333399, align: center"]Week[/TD]
[TD="bgcolor: #333399, align: center"]Game No.[/TD]
[TD="bgcolor: #333399, colspan: 2"]*[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399, colspan: 2"]*[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399, colspan: 2"]*[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399, colspan: 2"]*[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399, colspan: 2"]*[/TD]
[TD="bgcolor: #333399"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #333399, align: center"]1[/TD]
[TD="bgcolor: #333399, align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="bgcolor: #333399, align: center"]1[/TD]
[TD="bgcolor: #333399, align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #333399, align: center"]1[/TD]
[TD="bgcolor: #333399, align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="bgcolor: #333399, align: center"]1[/TD]
[TD="bgcolor: #333399, align: center"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #333399, align: center"]1[/TD]
[TD="bgcolor: #333399, align: center"]Tiebreak[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="bgcolor: #333399, align: center"]1[/TD]
[TD="bgcolor: #333399, align: center"]Points[/TD]
[TD="colspan: 3, align: center"]4[/TD]
[TD="colspan: 3, align: center"]3[/TD]
[TD="colspan: 3, align: center"]2[/TD]
[TD="colspan: 3, align: center"]1[/TD]
[TD="colspan: 3, align: center"]0[/TD]

</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
 
Upvote 0

Forum statistics

Threads
1,223,248
Messages
6,171,027
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top