Determine Five Best Scores

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Determine Five Best Scores

VinceB.
Can someone offer assistance to me to solve a problem, using AOO
4.1.3-CALC ?


I have typically 8 or 9 players in a bowling group.  We are currently
participating in a competitive tournament whereby I must submit a
5-member Team score results to the BVL Tournament administrator.  Here
is what I have:


Eight players bowled three games today. I have entered each players'
name in column A, followed by their individual scores in columns C, D,
and E.  I need to determine the five highest scores from the group of 8
players after each player finished 3 games of bowling.


Is their a function that would allow determination and display of the
five highest scores when considering 8x3=24 scores ?

The data looks something like this:

                 A                                B             C        
                 D                    E


       
        March 7, 2017
PLAYER NAMES
        GAME #1 GAME #2 GAME #3

       
       
       
       
Player#1
        156 111 165
Player#2
        168 211 178
Player#3
        109 127 97
Player#4
        148 157 136
Player#5
        114 127 134
Player#6
        127 166 138
Player#7
        146 197 177
Player#8
        167 169 121

       
       
       
       

        HIGH SCORE 168 211 178

        Next HIGH 1 167 197 177

        Next HIGH 2 156 169 165


In the above, the HIGH SCORE for columns C, D, and E were calculated
using =MAX( ); the Next HIGH 1 and Next HIGH 2 were eyeballed !

I need to submit the five highest scores for 7 Mar 2017; and repeat this
task for the remaining 3 tournament play sessions, 14, 21, and 28 Mar 2017.

I don't know if this requires a macro or a lookup table, neither of
which I have ever used in CALC.  Any help on this is appreciated very much.

Regards.

VinceB.

Reply | Threaded
Open this post in threaded view
|

Re: Determine Five Best Scores

Steven Ahlers
Vince,

I think there might be, but it might be more useful in this situation to use Base instead of Calc

Sent from my iPhone

> On Mar 7, 2017, at 6:39 PM, VinceB. <[hidden email]> wrote:
>
> Can someone offer assistance to me to solve a problem, using AOO 4.1.3-CALC ?
>
>
> I have typically 8 or 9 players in a bowling group.  We are currently participating in a competitive tournament whereby I must submit a 5-member Team score results to the BVL Tournament administrator.  Here is what I have:
>
>
> Eight players bowled three games today. I have entered each players' name in column A, followed by their individual scores in columns C, D, and E.  I need to determine the five highest scores from the group of 8 players after each player finished 3 games of bowling.
>
>
> Is their a function that would allow determination and display of the five highest scores when considering 8x3=24 scores ?
>
> The data looks something like this:
>
>                A                                B             C                        D                    E
>
>
>    
>    March 7, 2017
> PLAYER NAMES    
>    GAME #1    GAME #2    GAME #3
>
>    
>    
>    
>    
> Player#1    
>    156    111    165
> Player#2    
>    168    211    178
> Player#3    
>    109    127    97
> Player#4    
>    148    157    136
> Player#5    
>    114    127    134
> Player#6    
>    127    166    138
> Player#7    
>    146    197    177
> Player#8    
>    167    169    121
>
>    
>    
>    
>    
>
>    HIGH SCORE    168    211    178
>
>    Next HIGH 1    167    197    177
>
>    Next HIGH 2    156    169    165
>
>
> In the above, the HIGH SCORE for columns C, D, and E were calculated using =MAX( ); the Next HIGH 1 and Next HIGH 2 were eyeballed !
>
> I need to submit the five highest scores for 7 Mar 2017; and repeat this task for the remaining 3 tournament play sessions, 14, 21, and 28 Mar 2017.
>
> I don't know if this requires a macro or a lookup table, neither of which I have ever used in CALC.  Any help on this is appreciated very much.
>
> Regards.
>
> VinceB.
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Determine Five Best Scores

Brian Barker
In reply to this post by VinceB.
At 21:39 07/03/2017 -0500, Vince Bonly wrote:
>Can someone offer assistance to me to solve a problem, using AOO
>4.1.3-CALC ? I have typically 8 or 9 players in a bowling group ...

You'll forgive my giggling, but it always amuses me when people write
an essay on their life (but which may still not exactly specify the
problem) instead of paring their problem down to what they really need!

>I need to determine the five highest scores from the group of 8
>players after each player finished 3 games of bowling. Is there a
>function that would allow determination and display of the five
>highest scores when considering 8x3=24 scores ?

So you need five results from twenty-four values - not the nine you
have shown in your example (or fifteen if we extend the idea)?

Are your values in C3 to E10?

For your five results, use
=LARGE(C3:E10;1)
=LARGE(C3:E10;2)
=LARGE(C3:E10;3)
=LARGE(C3:E10;4)
=LARGE(C3:E10;5)

Even simpler (and more reliably), if you want these values in a
column, start with
=LARGE(C$3:E$10;ROW()-n)
- where "n" is a suitable value (depending on the row you choose for
this top value result) so that ROW()-n gives the result 1. Then fill
this formula down the five cells.

Note that this technique copes with duplicate values, so if there are
two equal third highest scores, say, both will appear in your list.
I'm guessing that this is what you will want.

I trust this helps.

Brian Barker


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Determine Five Best Scores

James Plante
@Brian: Thank you! I had not seen that function in use before.

@Vince: Take a look in the Help file for another explanation of the LARGE() and SMALL() functions. Since you’re keeping track of bowling scores, you might also want to take a look at the RANK() function. From your posts, I’m thinking it may come in handy later on.

Jim Plante

> On Mar 8, 2017, at 3:30 AM, Brian Barker <[hidden email]> wrote:
>
> At 21:39 07/03/2017 -0500, Vince Bonly wrote:
>> Can someone offer assistance to me to solve a problem, using AOO 4.1.3-CALC ? I have typically 8 or 9 players in a bowling group ...
>
> You'll forgive my giggling, but it always amuses me when people write an essay on their life (but which may still not exactly specify the problem) instead of paring their problem down to what they really need!
>
>> I need to determine the five highest scores from the group of 8 players after each player finished 3 games of bowling. Is there a function that would allow determination and display of the five highest scores when considering 8x3=24 scores ?
>
> So you need five results from twenty-four values - not the nine you have shown in your example (or fifteen if we extend the idea)?
>
> Are your values in C3 to E10?
>
> For your five results, use
> =LARGE(C3:E10;1)
> =LARGE(C3:E10;2)
> =LARGE(C3:E10;3)
> =LARGE(C3:E10;4)
> =LARGE(C3:E10;5)
>
> Even simpler (and more reliably), if you want these values in a column, start with
> =LARGE(C$3:E$10;ROW()-n)
> - where "n" is a suitable value (depending on the row you choose for this top value result) so that ROW()-n gives the result 1. Then fill this formula down the five cells.
>
> Note that this technique copes with duplicate values, so if there are two equal third highest scores, say, both will appear in your list. I'm guessing that this is what you will want.
>
> I trust this helps.
>
> Brian Barker
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Determine Five Best Scores

VinceB.


On 3/9/2017 8:52 AM, James Plante wrote:
> @Brian: Thank you! I had not seen that function in use before.
>
> @Vince: Take a look in the Help file for another explanation of the LARGE() and SMALL() functions. Since you’re keeping track of bowling scores, you might also want to take a look at the RANK() function. From your posts, I’m thinking it may come in handy later on.
>
> Jim Plante

Thanks Jim for the pointer; I will study the RANK function, and keep it
in mind for future usage, in particular, when tracking bowling scores.

I too was not aware of the LARGE( ) function until mentioned here by
Brian, and I am making progress.

In my experience, it is helpful being aware of the context of how CALC
is being utilized by others. Your heads up comment  about the RANK
function most likely would not have ever been written by you if you were
not somehow aware of my bowling score application with CALC.

Regards,
VinceB.


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]