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.

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

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.

Do you mean something like this?

norm.zip

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

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.

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?

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.

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

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