Ranking - Ties
I've a problem with scoring using the rank formula.
Example: four players score, 28,17,39,6. The 39 score wins, gets 4 points, 28 = 3points, 17 = 2 points, 6 = 1 point.
The rank formula will work this out fine.
However if you get two scores tied, for example 28, 28, 39, 6, using rank the points score for each 28 returns 2 when I actually need it to be 2.5
(Thats 3points +2points / 2).
Any ideas how to make this happen or even if it is possible?
Thanks.
Helper column may help u.
Put this formula into A1 and copy it down
In column B your values 28.28.39.6
Please Login or Register to view this content.
In C1 formula:
See filePlease Login or Register to view this content.
Last edited by contaminated; 06-29-2009 at 05:02 PM.
Люди, питающие благие намерения, как раз и становятся чудовищами.
Regards, ?Born in USSR?
Vusal M Dadashev
Baku, Azerbaijan
Thank you for such a rapid response.
I've looked at your attached file and sort of understand what you've done, however your result is that the two equal scores now receive different points when I actually require them to receive the same points, 2.5, which is 3 points for second + 2 points for third, divided by 2.
Difficult one eh?
No, its fairly easy. You want the points plus an additional value added only when there is more than one equal score
Take a look at my attached file
I have a lookup table in columns F and G
What you're after is column C + column D
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Sorry, i don't seem able to make myself clear. I've now added an attachment with 5 games & results. The second box is the result I get using the rank formula which is correct except when there is a tie, the third box (marked in yellow) is how the result should be.
Does this do what you want?
It's set up to calculate for only 2 way ties. An additional IF statement could take care of 3 way ties.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
You can use a single formula to cope with any number of ties, i.e. in G2 copied down and across
=SUMPRODUCT(($B2:$E2<B2)+($B2:$E2=B2)/2)+0.5
see attached
Last edited by daddylonglegs; 06-17-2009 at 02:57 PM.
Here is my approach...
Wow, thank you guys, I'm sure one of either of those will do the trick just fine. Much obliged.
Hi all, back again.
This problem is almost solved using the "choose" formula suggested, however although there are no scores registered yet, the formula still gives a points tally (Row 6) that is not valid.
Is it possible for the points to show as zero until an actual score is entered, even if that score is zero?
I would suggest using the formula provided by daddylonglegs - adapting per your layout.... given the way you have laid out the data I would keep R:U so you have the results linking into a contiguous range, remove N:Q as these aren't required.. then to get your values in C,F,I,L:
C3: =IF(COUNTIF($R3:$U3,"<>0"),SUMPRODUCT(($R3:$U3<B3)+($R3:$U3=B3)/2)+0.5,0)
copied down to rows 4 & 5 and subsequently copied to columns F, I & L
the above assumes that if any score in R:U <> 0 then any 0's that do exist are valid results to be allocated points accordingly.
Last edited by DonkeyOte; 07-06-2009 at 07:50 AM. Reason: altered reference to R3 to B3 so as to ease formula transition when applied across non contiguous range
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Yep, that's done it.
Thank you very, very, much.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks