Scoring help

**kabnt2005** · 05-15-2013, 08:47 PM

Help File.xlsx

NBVC, this one's for you buddy! (Or anyone else that can help me and beat him to it) You previously helped me with something that is a different part of this scoring spreadsheet. I've attached just the parts that I'm looking at to get your help on this current issue. I have a spreadsheet that I use to tabulate the shooting scores at a tournament for different shooters. Here's the important background information:

There are 6 events
Each event has a maximum score of 60
Each event gets a point total added to the "season total" based on how high the score is
56-60 gets you 6 points, 50-55 gets 5 points, 43-49 gets 4 points, 32-42 gets 3 points, 20-31 gets 2 points, 0-19 gets 1 point
Only the 2 best events have their points added to the "season total"

So I have put an example in the document. Shooter 1 has scored a 55 at Shoot 1, a 59 at Shoot 4, and a 58 at Shoot 5. These are shown in Cells K6:P6. So the points for each of those would be 5, 6, and 6, respectively. However, only the 6 and 6 will be counted in my total Cell D6. In Cells AD6:AO6, it calculates the points for each individual event. For instance, Cell AD6 corresponds to Cell K6. So if K6 is one of the 2 highest shoots, it will show a "5" in AD6. However, since it is not one of the 2 highest shoots, I want it to show blank ("").

I've put the coding into Cell AD6 for what I think should work, but it is showing "FALSE" instead of "". If K6 is one of the two highest scores, then it changes to "5" correctly. Can you help me, I'm stumped!

Thanks!

Kyle

**Ron Coderre** · 05-15-2013, 09:15 PM

Using your posted workbook...
This regular formula returns the appropriate score in AD:AO range
AD6: =IF(COUNT($K6:$P6)>1,IF(ISNUMBER(MATCH(K6+COLUMN(K6)/10000,INDEX(LARGE($K6:$P6+COLUMN($K6:$P6)/10000,{1,2}),0),0)),MATCH(K6,{0;20;32;43;50;56;0;0},1),0),0)

Copy that formula into AF6, AH6, etc...Be sure to change the K6 references to L6, M6, etc as appropriate
Example:
AF6: =IF(COUNT($K6:$P6)>1,IF(ISNUMBER(MATCH(L6+COLUMN(L6)/10000,INDEX(LARGE($K6:$P6+COLUMN($K6:$P6)/10000,{1,2}),0),0)),MATCH(L6,{0;20;32;43;50;56;0;0},1),0),0)

Is that something you can work with?

**kabnt2005** · 05-15-2013, 10:49 PM

Ron, I had to change the COUNT function to >0 since it is possible for someone to participate in only one event and still get points. Also, when I put in a score of 57 or higher, I get an "8" in the AD6 cell, so I had to take out the ";0;0" in the MATCH function. It appears to work now, not that I really understand the whole MATCH functions and the K6+COLUMN(K6)/10000 references.

**kabnt2005** · 05-16-2013, 01:41 PM

Ron, I entered everything into the cells AD6:AO6. In cell AN6 (referring to P6), I am getting a return of "1", when there is nothing entered in P6. If P6 has a value entered, it works fine. It also works fine if there are two scores higher than the value entered in P6. It only returns the "1" if there is only one score entered somewhere else in cells K6:O6.

This is what I have entered in AN6:
=IF(COUNT($K6:$P6)>0,IF(ISNUMBER(MATCH(P6+COLUMN(P6)/10000,INDEX(LARGE($K6:$P6+COLUMN($K6:$P6)/10000,{1,2}),0),0)),MATCH(P6,{0;20;32;43;50;56},1),""),"")

I think that if I change the COUNT reference from K6:P6 to just P6 (or the other respective cells in K6:O6), then it should work fine.

Thoughts?

Kyle

**kabnt2005** · 05-16-2013, 04:04 PM

Ron, I also have a similar scoring problem for one shoot that will score points no matter what the score is. The shoot's score will be entered in Cell F6.

445-450 = 6 pts, 432-444 = 5 pts, 409-431 = 4 pts, 365-408 = 3 pts, 310-364 = 2 pts, 0-309 = 1 pt

This will be entered into Cell Z6.

Thanks,

Kyle

**Ron Coderre** · 05-16-2013, 08:17 PM

Hi, Kyle...sorry for the delay.
OK...These are the necessary changes:

Put this regular formula into...

Formula:

Please Login or Register  to view this content.

and here's a shortcut for getting that formula, with edits, into the other cells.
• Copy AD6 and paste into AE6
• Cut AE6 and paste into AF6
• Copy AF6 and paste into AG6
• Cut AG6 and paste into AH6
(repeat until done)

For the tournament score...use this regular formula

Formula:

Please Login or Register  to view this content.

Does that help?

**kabnt2005** · 05-16-2013, 08:54 PM

Ron, no worries, I appreciate your help.

Absolutely helped, I just cleaned it up by replacing the zeros for the false portions of the IF functions with "", just to get rid of the zeros down the road.

I guess my only other question is if I can clean up some rankings too. For each event, the scores are ranked and points are assigned based on the shooter's placement at the shoot.

There will be a whole list of shooters who partake in the shoot in Column F. Also, there is a second score for tie-breaking purposes that goes in Column G. For instance, Shooter 2 will have a score in F7 and G7. If you have the highest rank, you get 4 points. Rank 2-5 gets you 3 points, 6-10 get 2 points, and 11-20 get 1 point.

So I need to compare the value F6 to all the other values in Column F (F7, F8, F9, ...) first and then the value G6 to all the other values in Column G. This ranking calculation will go in Cell AA6 for Shooter 1. This is what I have, let me know if that can be cleaned up at all. I know that it works, I just think it's way too long and would like to shorten it up if possible.

=IF(COUNT(F5)=0,"",IF(RANK(F5,$F$5:$F$103)+SUMPRODUCT(--($F$5:$F$103=$F5),--(G5<$G$5:$G$103))=1,4,IF(AND(RANK(F5,$F$5:$F$103)+SUMPRODUCT(--($F$5:$F$103=$F5),--(G5<$G$5:$G$103))>=2,RANK(F5,$F$5:$F$103)+SUMPRODUCT(--($F$5:$F$103=$F5),--(G5<$G$5:$G$103))<=5),3,IF(AND(RANK(F5,$F$5:$F$103)+SUMPRODUCT(--($F$5:$F$103=$F5),--(G5<$G$5:$G$103))>=6,RANK(F5,$F$5:$F$103)+SUMPRODUCT(--($F$5:$F$103=$F5),--(G5<$G$5:$G$103))<=10),2,IF(AND(RANK(F5,$F$5:$F$103)+SUMPRODUCT(--($F$5:$F$103=$F5),--(G5<$G$5:$G$103))>=11,RANK(F5,$F$5:$F$103)+SUMPRODUCT(--($F$5:$F$103=$F5),--(G5<$G$5:$G$103))<=20),1,IF(RANK(F5,$F$5:$F$103)+SUMPRODUCT(--($F$5:$F$103=$F5),--(G5<$G$5:$G$103))>20,0))))))

Thanks again for your help Ron!

Kyle