Questionnaire - transforming raw data into standardised one

[Peter3]

Hi, I will be very grateful if you can suggest a solution to the problem. I am doing a lot of questionnaire surveys, the results of which need to be standardized. I don't know how I should do it using Airtable. Currently, I have created two tables. Table 1 shows the questionnaire survey results (ws_score) and table 2 shows the standardized results. What should I do (what formula?) to substitute the standardized score from table 2 (sten) into table 1 (sten_number)?

 

[TheTimeSavingCo]

You could make a queue system to run an automation that would link the record from Table 1 to all the records in Table 2

You'd add lookup fields in Table 2 to display the `ws-score` value of the linked `Table 1` record, and use a formula field to figure out whether it's within the range

Then you'd use another automation to update the record in Table 1 with the right "Sten" value

If it was just via a formula, you could do something like:

IF(
  {Sex} = "Men",
  IF(
    AND(
      {ws-score} >= 0,
      {ws-score} < 97
    ),
    1,
    IF(
      AND(
        {ws-score} >= 97,
        {ws-score} < 105
      ),
      2,
      etc etc,
  )
)

[Peter3]

Thanks @TheTimeSavingCo , unfortunately I do not know how to deal with the creation of automation. Maybe I'll try with a nested if formula but I'm already terrified of its length....

[TheTimeSavingCo]

Yeah..best of luck with the formula dude