Weightlifting & Excel...Need a tiebreaking system
Posted by Brian on January 19, 2002 2:21 PM
I have made a sheet that helps score points for our states weightlifting competition. I had some help from a boardmember, Tom Dickinson as well.
Now, I have one final sticking point...what to do if there is a tie. I have posted my spreadsheet online at:
www.geocities.com/cwknightcoach
if you want to try to help out.
I eagerly await your help.
PS. I just bought a VBA for Dummies book so I can try and learn this stuff myself. It looks tough.
Posted by Aladin Akyurek on January 19, 2002 2:38 PM
Brian --
Use:
=RANK(R5,R$5:R$13,1)+COUNTIF(R$5:R5,R5)-1
instead of just
=RANK(R5,R$5:R$13,1)
Aladin
===========
Posted by Brian on January 19, 2002 4:35 PM
Aladin -
I used your code and it gives me an error. Says something about a circular reference. Please help again.
Posted by Aladin Akyurek on January 19, 2002 4:53 PM
That shouldn't occur.
The modified formula should be entered in S5.
BTW, when I opened your file, I immediately looked at column S, where ties occur, and suggested the formula with RANK + COUNTIF. That is, without having read the long note. Try re-enter the formula as is (should really work). The question is of course: Does it do what you want in the light of the long note?
Aladin
Posted by Brian on January 19, 2002 5:16 PM
Re: Weightlifting & Excel...Aladin...one more thing...pls
Aladin (Message 3):
I used the code this time and it did not give a circular reference. Good. But you mentioned was this what I was looking for since you didn't read long note.
Actually, I was looking to break ties in columns M O and Q (events) The long note explains the tiebreaker per event.
I I could get these three columns, I would be done. Your name is also going on this application...thanks in advance!
Brian
-
What I was looking for was trying to break ties that occur in columns M, O, & Q. The long note explains the tiebreaker per column. Any help much appreciated...
Posted by Tom Dickinson on January 20, 2002 5:47 PM
Re: Weightlifting & Excel...Aladin...one more thing...pls
From the way it looks, the tie breaker will need to be a macro. Especially when you realize that there could be a 3-way tie. If this is the case, you will want to start each match with the copy of the spreadsheet from the file (i.e., no data filled in as yet.) Otherwise you will have the macro's eliminatting formulas, and your sheet will be error prone.
You could insert another column at each place where there may be a tiebreaker, and have it flag cases where there is a tie. (This is not the remedy you seek, but it is a quick fix.) If the new column were N, and the tie column were M, then the formula
=if(countif(M$5:M$8,M5)>1,"***","")
This formula would show stars whenever there were 2 or more at the same ranking.
The ultimate macro you seek will take a little while to write and perfect.
Hope this helps.
By the way, my e-mail outgoing seems to be down, so I can't respond to your mail, but send any updates on your decision.
