Excel question

P-300 19:13 23 Feb 04
Locked

Can anyone give me a solution to this problem...?

In column A I have 20 scores, some of which may be the same. In column B I want to assign points from 20 for the highest down to 1 for the lowest, with points for equal scores being added together divided by the number of times the score is repeated. For example, if the top 6 scores were 250, 240, 230, 230, 230, 229, 229, then the three 230's would each receive (18+17+16)/3 points and the two 229's would receive (15+14)/2 points.

Maybe there is a simple solution, but this smacks of being a VBA macro. However I am not familiar enough with the programming to work it out for myself!!

...ps. if there were more than 20 scores the rest would be assigned 0 points.

Thanks in anticipation....

daba 20:51 23 Feb 04

I do have a solution to this, as I had to do similar for a pub quiz.

However it's on one of my backup CDs and will take me a while to find.

A point about the scoring system, though. It's usual for equal scores to receive the same points which would be allocated if they were the sole instance of that score. eg. in your example, the 3 230's would each score 18, not 17 as your /3 would give.

The reasoning here is that each of the teams scoring 230 could argue that they are being penalised a point (relative to the team scoring 240, for example), simply because another team has attained the same score as them.

This brings the problem a little easier to solve, because Excel has a "Rank" function, Look it up and see if does the job.

In the meantime, I'll try to find the code I wrote about 3 years ago.

daba 20:58 23 Feb 04

Formula in B1 =21-RANK(A1,A\$1:A\$20)

Copy this down

With acknowledgements to daba.

To ensure that 0 is the score for the teams who are not in the top 20 or so (it being possible to have more than 20 teams scoring), and to cater for the A column not having an entry then place the following code in B1 and copy down to wherever you wish.

=IF(A1="","",IF(21-RANK(A1,\$A:\$A)<=0,0,21-RANK(A1,\$A:\$A)))

daba 21:46 23 Feb 04

Haven't found my elusive backup yet, been playing that daft japanese flash thingy.

Just had a thought, is RANK in the standard Excel install or is in the analysis toolpak add-in ?

P-300 22:27 23 Feb 04

The Rank function appears to be a standard function in Excel (one that I was unaware of before) and the formula Whisperer has given works well.

I have marked this post as resolved though still wonder if there is a way of 'sharing' the points for multiple joint scores just to see if it can be achieved if nothing else...

Thanks again guys.

daba 22:26 24 Feb 04

It would be possible to write some VBA code (VoG will probably see this and step in with the code) to scan for duplicate RANK scores and adjust them downwards by 0.5 points for 2 consecutive entries, 1 point for 3 etc. etc. but do you really have to do it this way..?

As I said in my post, the recipients of "shared" points may be justifiably annoyed at losing out over "non-shared" points winners. This can be highlighted by an extreme (but nevertheless plausible), example :-

Take the case of 20 teams in a competition over several rounds.

Team A comes first in round 1 : earning 20 points

All the other teams come joint second, so would earn by your calculation 19/19 points = 1 !!

That gives team A a very definate and unassailable points advantage.

Is this a constitutional thingy in the organisation for which you are doing the scoring spreadsheet ?.....

Could it be changed in favour of the conventional scoring system that has no such drawbacks ?....

It would be so much easier all round, and fairer for the participants, don't you think.

P-300 23:23 24 Feb 04

daba, I think you misunderstood the points system assigned for equal scores.

If Team A finishes first and scores 20 points, then 3 teams finish equal second, each of these teams will receive (19+18+17)/3 = 18points. If two teams were tied in second they would then each receive (19+18)/2 = 18.5points. And, in your extreme example where all the other teams finish second: (19+18+....+2+1)/19 = 10points. Admittedly this could become ridiculously extreme when, say, 30 teams finish in second in which case the total points will be divided by 30 (=6.33points) - So be it!!

To give an example why it is preferred to be this way... when golfers are classified in order their prize money is distributed from 1st to whatever, with all tied scores receiving the cash for, say 3rd to 7th if tied in third, divided by the number of players tied in that position, i.e. 5.

By the way, it is not me making up the rules!!!

VoG II 23:31 24 Feb 04

In column A, are the scores in order? If so, increasing or decreasing?

It may be possible to do this in VBA but depending on your timescale as I'm tied up until the weekend.

P-300 00:08 25 Feb 04

Ah, Voice of God, I bow to you Sir!!

The scores in column A will NOT necessarily be in order. In fact, to scale up what I am trying to achieve, the scores in column A and points ranking in column B will be effectively repeated in column C&D, E&F etc for the number of 'rounds' played and eventually totalled in another pair of columns. In our organisation there could be a varying number of teams/players, but only the top 20, say, will receive ranking points in each round.

Hope this helps...

daba 20:20 25 Feb 04

P-300

There was no misunderstanding, I fully appreciated what your system was doing, and that is why I pointed out the possible 'unfairness'.

The 'sharing' you mention is perfectly OK when applied to a Single-Round competition, as there is a fixed amount of prize-fund to be awarded.

However, from your last post, which is the first mention of 'rounds', am I correct in thinking that the points attained, per round, will be allocated on the traditional 'Ranking' principle, and that your 'prize sharing' is only done on the sum of the scores at the end of the competition ? If you applied your sharing maths to all rounds, the unfairness would still be apparent. At the end of the day, the final 'Scores' should be a record of achievement, not a measure of a team's ability to produce a unique score for the round !

I thank you for pointing out my math error, the "shared" score should be (19+18+17.....+1)/19 = 10 exactly as you say.

As for a solution to the problem, I'll have a go, but I'm certain that the code will almost certainly have to have the final scores data sorted into asc. or desc. to be able to detect the multiple 'same' scores more easily. If you don't want the data sorted it could be copied into another worksheet before running the code.

I'll post back with a solution if I get it right.

This thread is now locked and can not be replied to.

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

An overview: What leading creative agencies are doing to improve diversity

New iPad, iPhone SE & Red iPhone 7 on sale now