Automatically Calculating Norms

[Scales]

I need to find out if it is possible to calculate norms in FM. Here's my scenario:

I've got 30 records in a portal that displays scores on Test A for all third graders in School 123. Three students received a 98 on the test, which was the top score in the class. If I were calculating the norm by hand I would write the following equation:

((.5) 3 + 27)/30 (3 is the number of students with the score, 27 students fall below the score, and there are a total of 30 students in the class. In other words, the calculation would be

(.5x +y)/n

where x equals the number of students with a particular score, y equals the number of students with scores below that particular score, and n equals the number of students in the class (or portal)

I know how to count the total number of records in a portal (n), but I don't know how to count the portion of records in the portal that meet particular criteria (x and y). Is it possible to do that?

Any help is much appreciated.

[Lee Smith]

I sent you a private message about double posting.

The following is the body of the post I deleted (twice).

I posted this same problem in different words in the "Define Fields" forum and never got any responses, so I am trying again. Here's a simplified version of my calculation issue:

I have a database with fields ClassID, StudentID, and Score. (Teachers use this database.)

In a separate database, I have set up portals and relationships so that administrators can input a class ID number and then see a list of everyone in the class and their scores on the test.

What I need to be able to do is recreate the following formula in FileMaker to get the percentile rank:

x=# of students with a particular score

y=# of students with scores below that particular score

n=total number of students in class (I know how to get this number)

((.5(x) + y)/n) * 100

This seems to me like it should be so straight forward, but I'm just not getting anywhere. Any thoughts?

Many thanks in advance.

Lee

[-Queue-]

If you could post your file or a smaller copy of it, it would be helpful. There are too many ways your tables could be set up to give a definite solution.

[comment]

Do you mean something like this?

norm.zip

[Razumovsky]

I think I get what you are trying to do. In FM6 I would do the following:

Create a selfjoin relationship of TestName::TestName that would link all Test A records to each other, all Test B records to each other, etc...

then create a num calc field:

Case(TestScore = Max(SelfJoin::TestScore), 1, 0)

This will return a "1" for your 3 students with the same highest score, and a "0" for the other 27.

Then it is a simple matter of tallying the 1's and 0's

X would be sum(YourNewCalcField)

Y would be n-X

There might be an easier way in FM7, but I don't consider the above that complicated.

Does that help?

-Raz

[-Queue-]

Comment made it simple. I was envisioning entities for Classes, Students, Tests, Scores, and join tables for them, which starts getting complicated after a while.

Raz, TestScore = Max(SelfJoin::TestScore) will give you the same result.

[Razumovsky]

Okay, enough of dipping the toes in FM7, I need to dive in. Those conditional relationships are great!

Queue- not sure what you mean, will give the same result as what?

[-Queue-]

The same result as Case( TestScore = Max(SelfJoin::TestScore), 1, 0 ). A = B is the same as Case( A = B, 1, 0 ), since both are boolean results.

[Razumovsky]

Ah yes, the case is redundant. I thought you meant it would give the same result for each record. Thanks for keeping me on my toes!

-raz

[Scales]

That's exactly it. Beautifully simple. Thank you so much.