counting number of times a # in a field > than 5

[papacubby]

I have over 200 people that did push ups 5 different times. Each time they did puch ups the score are recorded into FMP (PU#1, PU#2, PU#3, PU#4, PU#5).

What I would like to do now is create a field calulation that will show the percentage of times "BOBBY" did more than 5 push ups? Is there an easy formula for this? It is now 2:00am where I am at and I can not figure it out. "Novice" using FMP 6/0v4 WIN XP.. Any help would be thankful..

Thanks

[ESpringer]

Ideally, you'd do this with two tables: one holds people, and the other holds data for a given "trial". In the Trials file, Bobby would have five different records. Information about their average would be accessed by a *relation* between the Persons record and the related Trials records. This would be easier because right now you've got five different field holding essentially similar data. If there are various things you always want to know about particular trials, you need to set up a five calculations to extract the information. Meanwhile, if you set up a calc field in a separate Trials file (such as OverFive?), it would neatly apply to each trial. Another advantage is that if you have 5 trials for one person and 20 for another, you can still manage, without creating 20 fields for a person's various trials.

Anyvay, if you need to do this *without* altering your database structure, it's possible.

Set up a calc field like this:

Average (If(PU#1>5,1,0),If(PU#2>5,1,0),If(PU#3>5,1,0),If(PU#4>5,1,0),If(PU#5>5,1,0))

Essentially, the calc says: take the average of five numbers, 1 for when there was a trial of more than 5, 0 for when there was a trial of 5 or less. On your layout, get this number (which will always be between 0 and 1) to display as a percent.

If you want to be able to change the threshold "on the fly", set up a global number field called Threshold, and make you calc refer to that instead of directly to the number 5 for each trial.

[papacubby]

Wow, very well explained.. I will give it a try tonight and edit or repost how it went. Thanks you very much for your help!!!

PC

[bruceR]

FWIW the calc can be shortened to the following because a TRUE statement evaluates to 1.

Average ( (PU#1>5), (PU#2>5), (PU#3>5), (PU#3>5), (PU#4>5),(PU#5>5) )

[papacubby]

I got it working.. Thanks for your help...

PC