Calculate median values from likert scale survey responses (Google Sheets)

Question

I’m trying to calculate the median values from Likert scale survey survey responses in Google Sheets and need a bit of help.

Some context on the survey:

1. Users can select any number of tasks
2. Users rate Importance
3. Users rare Satisfaction

As a result there won’t be a consistent number of tasks selected.

My current data set:

Collector ID | Response ID | Tasks   | Importance | Satisfaction
-------------------------------------------------------------------
123          | 789         | Task #1 | 1          | 4
123          | 789         | Task #2 | 4          | 3
456          | 654         | Task #1 | 3          | 2
456          | 654         | Task #2 | 2          | 5
789          | 123         | Task #1 | 4          | 2

What I’m trying to do is loop through each task and return a median value of both the Importance and Satisfaction in a results sheet. I then plan to plot these two values on a scatter graph using the tasks as a label.

Desired outcome:

Task    | Importance (median) | Satisfaction (median)
-----------------------------------------------------
Task #1 | 3                   | 2
Task #2 | 3                   | 4
…

I’m fairly new to spreadsheet formulas so any advice would be greatly appreciated.

Thanks for taking a look.

Answer 1

use:

=INDEX({UNIQUE(FILTER(C2:C, C2:C<>"")), 
 BYROW(SPLIT(BYROW(UNIQUE(FILTER(C2:C, C2:C<>"")), LAMBDA(xx, 
 TEXTJOIN("×", 1, FILTER(D:D, C:C=xx)))), "×"), LAMBDA(xx, MEDIAN(xx))), 
 BYROW(SPLIT(BYROW(UNIQUE(FILTER(C2:C, C2:C<>"")), LAMBDA(xx, 
 TEXTJOIN("×", 1, FILTER(E:E, C:C=xx)))), "×"), LAMBDA(xx, MEDIAN(xx)))})