What I have is some data in this format:
<tbody>
</tbody>
<tbody>
</tbody>
This tells me that for question 1, 14 people responded, with 7 picking "1", 4 picking "2" and 1 each picking "3", "4", and "5". Same pattern for subsequent questions; and, as you can see, the questions did not need to be answered at all, so the # of respondents changes.
It's easy enough to just build an array for each question to find the median -- e.g., question 1. [ 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 3, 4, 5 ], median 1 or 2 (or 1.5) -- but there are 54 questions not represented here, and then I have to do it all again with the next survey, etc. Is there a way to utilize MEDIAN(), telling it the structure of the array using the data provided? Or a way to automatically build an array by laying out values in some hidden cells based on the data provided?
| How well does <statement> apply to you? (1 = Entirely, 5 = Not at All)</statement> |
<tbody>
</tbody>
| Question # | 1 | 2 | 3 | 4 | 5 | # Responding |
| 1 | 7 | 4 | 1 | 1 | 1 | 14 |
| 2 | 0 | 0 | 1 | 1 | 2 | 4 |
| 3 | 1 | 4 | 2 | 0 | 1 | 8 |
<tbody>
</tbody>
This tells me that for question 1, 14 people responded, with 7 picking "1", 4 picking "2" and 1 each picking "3", "4", and "5". Same pattern for subsequent questions; and, as you can see, the questions did not need to be answered at all, so the # of respondents changes.
It's easy enough to just build an array for each question to find the median -- e.g., question 1. [ 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 3, 4, 5 ], median 1 or 2 (or 1.5) -- but there are 54 questions not represented here, and then I have to do it all again with the next survey, etc. Is there a way to utilize MEDIAN(), telling it the structure of the array using the data provided? Or a way to automatically build an array by laying out values in some hidden cells based on the data provided?
