Google Sheets: Producing Norms for Respective Google Form Responses

As a psychology practice, we often get clients to fill in questionnaire data which is later converted and scored so our clinicians can read this data. At the moment, we have a system so that the Google Form answers are converted to Google Sheets and we have entered some formulas for them to be scored. However, we want to add formulas so that we can now calculate the average norm data and compare it to our clients. At the moment we are using a formula like so - =IF(OR(ISBLANK(#REF!)), "",if(AU6>6.06,"above norm","normal")) Basically, we are using an average data point of a norm to figure out whether the client’s score is above or below the norm of 6.06. However, our issue is that this norm is actually just averaged and we want to figure out the norm for specific age brackets and genders. We need this done for a quite a few separate questionnaires x5), but we can always do it ourselves too if we just get the basic outline of the formula. If it helps, for one of the questionnaires, we can also provide some SPSS syntax (see attached) that is used in someone else’s automatic norm spreadsheet. The two questionnaires we need the norms for is the Spence Children's Anxiety Scale (x2 types of questionnaires: child and parent) and Revised Children's Anxiety Scale (x3 types of questionnaires: child, parent and preschool). You can find these norms online if you want some more information. Project objectives - We would like the norms to automatically populate when the Google Forms response comes through (scoring has already been done) - We would like these norms for 5 different scales (RCADS – Parent, RCADS – Child, Spence Preschool, Spence Parent, Spence Child) - We would like to be informed about how and which formula was used to complete this task so we have some basic knowledge if we need to edit these in the future If you believe you can complete this task, we ca later provide you with the document link and norms (you can also find these norms very easily online by looking up RCADS Scale or Spence Scale). DETAILED EXAMPLE OF WHAT WE NEED For the two RCADS questionnaires, you can find them here along with instructions of what the norm should be according to the table (see scoring sheet at the bottom): [login to view URL] [login to view URL] This is the referred to normative data: [login to view URL] For the Spence scales, you can find the sheet with instructions and examples here (see scoring tab at the bottom): [login to view URL] You can find the normative data here: [login to view URL]