Hello all! I'm not sure what I'm after can be done, but figured I'd ask anyway. I'm setting up a spreadsheet to score a 66-question assessment that rates 8 categories of data.
The raw score for each category corresponds with a T-score (kind of like a percentile) that is adjusted for age range and gender. The paper version has two very large tables, one each for male and female respondents, that has 8 columns (one for each category), each of which has 4 sub-columns (one for each age range). To score, one picks the appropriate respondent gender table, category, and column for the respondent's age, and then finds the correct T-score for the raw score. With me so far?
In my spreadsheet so far, there are cells to enter the respondent's age and gender.
The spreadsheet takes the 66 answers and calculates a raw score for each of the 8 categories and puts it in one of two tables, one for male respondents and one for female respondents. Each table has four columns that represent age ranges.
I set up separate tabs for male and female respondents, and each tab has a table for each age range. See screenshots below:
I want the spreadsheet to use the age, gender and raw score cells for each category to select the correct tab, table and column, match the raw score to the appropriate T-score, and populate the T-score in the appropriate cell of the raw score tables of the main spreadsheet. Still with me?
For a 34 year-old female respondent, I want the spreadsheet to select these raw scores:
Select this tab:
This table:
And then use each category's raw score to select the corresponding T-score and return it to the main spreadsheet:
If Excel can in fact do something like this, any help is appreciated. Thank you!!
The raw score for each category corresponds with a T-score (kind of like a percentile) that is adjusted for age range and gender. The paper version has two very large tables, one each for male and female respondents, that has 8 columns (one for each category), each of which has 4 sub-columns (one for each age range). To score, one picks the appropriate respondent gender table, category, and column for the respondent's age, and then finds the correct T-score for the raw score. With me so far?
In my spreadsheet so far, there are cells to enter the respondent's age and gender.
The spreadsheet takes the 66 answers and calculates a raw score for each of the 8 categories and puts it in one of two tables, one for male respondents and one for female respondents. Each table has four columns that represent age ranges.
I set up separate tabs for male and female respondents, and each tab has a table for each age range. See screenshots below:
I want the spreadsheet to use the age, gender and raw score cells for each category to select the correct tab, table and column, match the raw score to the appropriate T-score, and populate the T-score in the appropriate cell of the raw score tables of the main spreadsheet. Still with me?
For a 34 year-old female respondent, I want the spreadsheet to select these raw scores:
Select this tab:
This table:
And then use each category's raw score to select the corresponding T-score and return it to the main spreadsheet:
If Excel can in fact do something like this, any help is appreciated. Thank you!!
