Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Questionnaire - transforming raw data into standardised one

844 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Peter3
4 - Data Explorer
4 - Data Explorer

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)?

Screenshot 2022-12-17 at 12.03.11.png

 

3 Replies 3

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,
  )
)

 

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....

Yeah..best of luck with the formula dude