I need to break a tie based on time. I am running a fishing tournament and need to rank boats by fish size. It's possible that multiple boats may weigh in a fish weighing the same. I need to break the tie using the time they weighed in. The formula I'm using is below. Any help would be greatly appreciated.
=SUMPRODUCT((B1<B$1:B$31)/COUNTIF(B$1:B$31,B$1:B$31))+1
Column A is Boat Column B is the weight. Column C is ranking and column D is time
columns.JPG
Hi,
Try this in your ranking column:
Formula:
Please Login or Register to view this content.
Regards,
Aardigspook
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.
In E2 copied down
=B2*10-D2
Then Rank using column E
=RANK(E2, $E$2:$E$20)
See attachment (I hid Column E)
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
Welcome to the Forum salberts1,
In order to enable a consistent rank order I subtracted the time of arrival from the hours remaining in the day and then added that to the weigh-ins.
It looks like this.
BTW since you are new to the Forum you might not be aware of the Forum preference for uploading a sample Excel file rather than a pic or screen shot. It saves having to retype your data and not everyone's browser is capable of seeing all of those .... not to mention company firewalls etc....
Row\Col Who Weigh-ins Raw Rank Time arrived Final Ranks Helper remaining time Combined scores Boat 1 In F2 =IF(B2,24-D2,"") Boat 2 In G2 =IF(B2,B2+F2,"") Boat 3 In E2 =IFERROR(RANK(G2,$G$2:$G$12,0),"") Boat 4 Boat 5 Boat 6 Boat 7 Boat 8 Boat 9 Boat 10 Boat 11
Does this help?
Dave
Just some more forum advice. You never clarified how the time should be taken into account in the ranking.
It may seem obvious to you, but clarification is extremely important in the forum.
Explaining that you want a formula that would have Taz ranked as 2, and Doc ranked as 3, based on the fact there weight is equal, with the earlier time ranking Taz higher, is much clearer.
Please click the * icon below if I have helped.
This didn't work. I now have this:
after_array.JPG
To which post is this addressed?Originally Posted by salberts1
I tried this and got the result i pasted in below.Hi,
Try this in your ranking column:
Formula:Please Login or Register to view this content.
Regards,
Aardigspook
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.
@ aardigspook...
Attached is what I see after applying the formula. And to be clear, in case of a tie, the first person to weigh in (earliest time) should win.
Thanks
after array.xlsx
This solved my issue. Thanks!
You need to insert the formula I gave you in cell C2, not C1.
I've assumed that all the entries will have a weight and a time against them by the end of the competition, so haven't tried to account for zeroes or blanks, which is why Boat7/Dopey gives a slightly strange result. If you think you will have results with zeroes or blanks, let us know and we can give it a try.
Edit: glad you got it working - for some reason I assumed you wouldn't want a helper column - no idea why - probably my stupidity
Regards,
Aardigspook
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.
Last edited by Aardigspook; 09-02-2015 at 04:10 PM. Reason: problem solved another way
For interest's sake, here's the formula (for C2) which ignores blanks and zeroes:
Formula:Please Login or Register to view this content.
If there's a blank in either column B or D (weight), or a zero in column D (time), this returns a blank and doesn't count it in the results.
If there's a zero in column B, but a valid time in column D, this is counted, as I assumed you'd want to count boats which came back with no catch.If you want to count all boats with no catch as equal last, use this formula instead:Here's the file with these working:
Formula:Please Login or Register to view this content.
Ranking two columns including time - for salberts1.xlsx
Sorry to continue when you've already got your solution...
Regards,
Aardigspook
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks