Multiple conditions to reach a certain value

[Raf]

Hello Community,
My question has probably already been asked on this forum but I’m not good enough in English to find it…forgive me by advance.
I’m trying to make an app which calculates a StandardDeviation score in an assessment.
The math would be : PatientStandardDeviation = (PatientScore - AverageScore) / Average StandardDeviation
BUT
The AverageScore this operation needs, depends on :

• the patient’s Age,
• his Socio-Cultural Level,
• his Gender,

So I took a Calculator template and created a few “Choices”,
one for Age range (20-49y / 50-74y / 75+ years),
one for Socio-Cultural Level (SCL1 or SCL2),
one for Gender (M or F).

The only thing I don’t know is how to make a path to reach the good AverageScore when it’s calculating the PatientStandardDeviation, depending on these conditions (Age/SCL/Gender).
Can someone help me here ?

I’m sorry if I’m not clear, here’s a screenshot of the app

It’s in French but it’s pretty similar (PatientScore = Score du Patient, SCL = niveau socioculturel, Gender= Sexe).

Thanks by advance for your kind help!
Raf

[Jeff_Hager]

Can you show some screenshots of your math columns? How is Average Score supposed to be calculated? Is it an average of previous submissions?

[Raf]

Thanks for your quick answer.

Average score wouldn’t be calculated, it’s an established score calculated by scientists.

Here’s how it is supposed to work:
1/ People enter their test score in PatientScore.
2/ They choose Male or Female, SCL 1 or 2, etc.
3/ Depending on these previous answers in 2/, PatientScore is calculated thanks to the AveragePatientScore value fitting their criterias (Male & SCL1 for example).
Behind the scenes in the sheet I will add every Average scores and Average Standard Deviation for every possibility (Female & SCL1, Male & SCL1, Female & SCL2, Male & SCL2).

For example :
I had 35/40 at my test.
I’m a man with SCL2, and the average score for this population is 36/40 and average standard deviation is 1.5.
The math would be (35-36)/1.5 = -0.66 DS (Deviation Standard).
Then the app would display the result : -0.66 DS.

My math column isn’t done yet but if you really need it I will do a side app in English to show you.
Again thanks for your time.
Raf

[Jeff_Hager]

OK, so I will assume that you have or will have a separate table that contains all combinations of those average scores. In that table, I would create a template column that joins the Age, SC Level, and Gender into one combined column. This will be your unique key value for that table. In the table that contains the math column, you would create a template that joins all of the values from the choices. Then create a Relation column that links that template to the template in the table that contains all of the average data. Then you would create a Lookup column that returns the correct average score from that relation. That’s the value you can use in your math column.