# ID and ave. the best two golf scores for a record

This topic is 6494 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

I have two tables: Players and Scores. Each player has multiple scores. I need to identiy and average the two best scores for each player while not including players with only one score. I don't know how to average only the two best scores.

FileMaker Version: 7

Platform: Mac OS X Panther

##### Share on other sites

Anywhere from 1-6 scores for 75-80 players. I can manually identify the two best scores but it would be nice to do it automatically and average the two. I then post scoring average for all of the scores for a player and the best 2 average.

HELP!

##### Share on other sites

The simplest way I can think of doing this is to use a related valuelist of scores. The valuelist is automatically sorted in ascending order, so the two best (lowest) scores will be the first two items in the list. You can retrieve them using

LeftWords(Valulistitems(blah blah),1) to get the lowest score and

MiddleWords(Valulistitems(blah blah),2,1) to get the second lowest score.

However, if the two lowest scores are the same, the valuelist will omit one of them. So, you have to go through a couple of hoops to correct for this. Rather than go through the detailed explanation, I have attached a sample file that does the calculation. It's an fp5 file. If you are using FM7, you can convert it.

FileMaker Version: 6

Platform: Mac OS 9

GolfAverage.zip

##### Share on other sites

You can overcome the problems of duplicate scores by making a calculated field = Score & " " & Serial Number. Make your value list from this field.

##### Share on other sites

You guys never cease to amaze me.... The clever ways you work the data just blows me away. For people like me numbers are numbers and I only think number fuctions. To you guys it's data and you just know how to manipulate the data to get what you need. That's way outside my box.

Thanks so much...

##### Share on other sites

This topic is 6494 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account