Is there and easier way?

[papacubby]

Is there and easier way to write this?  I need to return a result based on gender, age and Subtest4totalPtScore?  Any help would be great.  The below returns the correct result based on "Subtest4totalPtScore" - but only for Male, 11 years old.  I need to add other ages and females.  Thanks

 

If(Subtest4TotalPtScore="";"";
If(and(Subtest4TotalPtScore ≥ 0; Subtest4TotalPtScore < 5) and Gender = "M" and TestDateChronologicalAgeYear = "11";"1";
If(and(Subtest4TotalPtScore ≥ 5; Subtest4TotalPtScore < 9) and Gender = "M" and TestDateChronologicalAgeYear = "11";"2";
If(and(Subtest4TotalPtScore ≥ 9; Subtest4TotalPtScore < 14) and Gender = "M" and TestDateChronologicalAgeYear = "11";"3";
If(and(Subtest4TotalPtScore ≥ 14; Subtest4TotalPtScore < 18) and Gender = "M" and TestDateChronologicalAgeYear = "11";"4";
If(and(Subtest4TotalPtScore ≥ 18; Subtest4TotalPtScore < 22) and Gender = "M" and TestDateChronologicalAgeYear = "11";"5";
If(and(Subtest4TotalPtScore ≥ 22; Subtest4TotalPtScore < 25) and Gender = "M" and TestDateChronologicalAgeYear = "11";"6";
If(and(Subtest4TotalPtScore ≥ 25; Subtest4TotalPtScore <28) and Gender = "M" and TestDateChronologicalAgeYear = "11";"7";
If(Subtest4TotalPtScore = 28 and Gender = "M" and TestDateChronologicalAgeYear = "11";"8";
If(Subtest4TotalPtScore = 29 and Gender = "M" and TestDateChronologicalAgeYear = "11";"9";
If(Subtest4TotalPtScore = 30 and Gender = "M"and TestDateChronologicalAgeYear = "11";"10";
If(Subtest4TotalPtScore = 31 and Gender = "M" and TestDateChronologicalAgeYear = "11";"11";
If(Subtest4TotalPtScore = 32 and Gender = "M" and TestDateChronologicalAgeYear = "11";"12";
If(Subtest4TotalPtScore = 33 and Gender = "M" and TestDateChronologicalAgeYear = "11";"13";
If(Subtest4TotalPtScore = 34 and Gender = "M" and TestDateChronologicalAgeYear = "11";"16";
If(Subtest4TotalPtScore = 35 and Gender = "M" and TestDateChronologicalAgeYear = "11";"18";
If(Subtest4TotalPtScore = 36 and Gender = "M" and TestDateChronologicalAgeYear = "11";"20";
If(Subtest4TotalPtScore = 37 and Gender = "M" and TestDateChronologicalAgeYear = "11";"23"; ""))))))))))))))))))

[LaRetta]

Hi Papa,

 

Here is one way.  It's too bad because you almost have patterns which would make it even easier.  You might also seriously consider a table with the values if there is ever possibility of it changing.

 

If (
Gender = "M" and TestDateChronologicalAgeYear = 11 ;
Case ( 
Subtest4TotalPtScore < 5 ; 1 ;
Subtest4TotalPtScore < 9 ; 2 ;
Subtest4TotalPtScore < 14 ; 3 ;
Subtest4TotalPtScore < 18 ; 4 ;
Subtest4TotalPtScore < 22 ; 5 ;
Subtest4TotalPtScore < 25 ; 6 ;
Subtest4TotalPtScore < 28 ; 7 ;
Subtest4TotalPtScore = 28 ; 8 ;
Subtest4TotalPtScore = 29 ; 9 ;
Subtest4TotalPtScore = 30 ; 10 ;
Subtest4TotalPtScore = 31 ; 11 ;
Subtest4TotalPtScore = 32 ; 12 ;
Subtest4TotalPtScore = 33 ; 13 ;
Subtest4TotalPtScore = 34 ; 16 ;
Subtest4TotalPtScore = 35 ; 18 ;
Subtest4TotalPtScore = 36 ; 20 ;
Subtest4TotalPtScore = 37 ; 23
) // end case
) // end if

 

[papacubby]

Thanks...  This looks so much cleaner.  

However,  Any suggestions for if gender + "F"?   Any suggestions for if TestDateChronologicalAgeYear =12  

 

If (
Gender = "M" and TestDateChronologicalAgeYear = 12 ;
Case (
Subtest4TotalPtScore < 5 ; 2 ;
Subtest4TotalPtScore < 9 ; 3 ;
Subtest4TotalPtScore < 14 ; 4 ;
Subtest4TotalPtScore < 18 ; 5 ;
Subtest4TotalPtScore < 22 ; 6 ;
Subtest4TotalPtScore < 25 ; 7 ;
Subtest4TotalPtScore < 28 ; 8 ;
Subtest4TotalPtScore = 28 ; 9 ;
Subtest4TotalPtScore = 29 ; 9 ;
Subtest4TotalPtScore = 30 ; 10 ;
Subtest4TotalPtScore = 31 ; 13 ;
Subtest4TotalPtScore = 32 ; 12 ;
Subtest4TotalPtScore = 33 ; 11 ;
Subtest4TotalPtScore = 34 ; 11 ;
Subtest4TotalPtScore = 35 ; 11 ;
Subtest4TotalPtScore = 36 ; 21 ;
Subtest4TotalPtScore = 37 ; 28;

IF (
Gender = "M" and TestDateChronologicalAgeYear = 11 ;
Case (
Subtest4TotalPtScore < 5 ; 1 ;
Subtest4TotalPtScore < 9 ; 2 ;
Subtest4TotalPtScore < 14 ; 3 ;
Subtest4TotalPtScore < 18 ; 4 ;
Subtest4TotalPtScore < 22 ; 5 ;
Subtest4TotalPtScore < 25 ; 6 ;
Subtest4TotalPtScore < 28 ; 7 ;
Subtest4TotalPtScore = 28 ; 8 ;
Subtest4TotalPtScore = 29 ; 9 ;
Subtest4TotalPtScore = 30 ; 10 ;
Subtest4TotalPtScore = 31 ; 11 ;
Subtest4TotalPtScore = 32 ; 12 ;
Subtest4TotalPtScore = 33 ; 13 ;
Subtest4TotalPtScore = 34 ; 16 ;
Subtest4TotalPtScore = 35 ; 18 ;
Subtest4TotalPtScore = 36 ; 20 ;
Subtest4TotalPtScore = 37 ; 23;

IF (
Gender = "F" and TestDateChronologicalAgeYear = 11 ;
Case (
Subtest4TotalPtScore < 5 ; 11 ;
Subtest4TotalPtScore < 9 ; 21 ;
Subtest4TotalPtScore < 14 ; 31 ;
Subtest4TotalPtScore < 18 ; 41 ;
Subtest4TotalPtScore < 22 ; 51 ;
Subtest4TotalPtScore < 25 ; 6 ;
Subtest4TotalPtScore < 28 ; 7 ;
Subtest4TotalPtScore = 28 ; 8 ;
Subtest4TotalPtScore = 29 ; 9 ;
Subtest4TotalPtScore = 30 ; 10 ;
Subtest4TotalPtScore = 31 ; 11 ;
Subtest4TotalPtScore = 32 ; 12 ;
Subtest4TotalPtScore = 33 ; 13 ;
Subtest4TotalPtScore = 34 ; 16 ;
Subtest4TotalPtScore = 35 ; 18 ;
Subtest4TotalPtScore = 36 ; 20 ;
Subtest4TotalPtScore = 37 ; 23
) // end case
) // end if

[LaRetta]

And I started with If() to show you the difference between If() and Case() because you only have M and F for Gender, Age 11.  I knew better.  If only allows two options so you must change it to Case() and drop the idea of imbedding multiple If()s.

 

But really ... you need a table to hold these values.  Can you tell me what these resultant numbers represent, what this calculation is used for and where these values come from?

[papacubby]

It's the results of a Bruininks-Oseretsky test of Motor Proficiency test.  In a school setting,  students do a group of skills for balance,  they do another for stength, they do another for running speed.  They score points for each group of test.  I'm now working with taking each group of scores (balance being one) and giving it a new number.

 

This new number will be used to show how many standard deviations they are away from the mean (or for their age).  I'm taking the score they earned in ballance tests and giving them a new value based on age, gender and what they scored in the group of test.  

 

Again, this his new number that is returned, I will be able to then show SD, age equiv, ect.  The hard part is I have three variables to consider before returning a new number score.  

 

Hope I was able to explain it better.    My goal is to enter the scores for students on the test and print the results.  Thus, being done....

[LaRetta]

All the more reason to use a related table.   You can use script to create all the records - just need to know the age range and score range.  And even use this calculation to fill in the scores.  Then simply place this related score field on your Test layout.

 

---- added ---

 

Then relate as:

Test::Score = Scores::Score

and

Test::Age = Scores::Age

and

Test::Gender = Scores::Gender

 

---

 

You may need to adjust a bit ... I do not know how you currently are related and this wouldn't be difficult to pull together.  I'm surprised that there isn't already a database with these figures but a quick search doesn't show one. :^)

Edited February 17, 2013 by LaRetta

[papacubby]

Sounds like a plan.... Thanks for the help.... New table will be the best way to pull it together.  Can't be any harder than what I been banging my head, trying to do.