I'd greatly appreciate any help on how to set up data correctly in Excel for this analysis (excel professional plus 2016). I'm hoping to be able to analyse feedback collected from training participants over many training sessions. I'm unsure how to set this up in excel.
EDIT: Sample Data here: https://1drv.ms/f/s!AlCbiL7HoDTPgakK1zp8xUI_gRnctw
Currently feedback data is entered into Access using the following way:
Each feedback form received form a participant is added to Access as a record containing data as per this image. Raw Feedback Data
The numbers in the Questions column, correspond to a rating provided on the form. e.g. 5- Strongly Agree 4 = Agree etc.
I've 'mapped' these the scores to a rating (another table in Access) using a different table:Feedback category mapping
I would like to export the data to excel to be able to analyse and visualise the data according to the ratings. E.g. For each question/item on the feedback questionnaire, what is the proportion rated "excellent" "good", "fair" or "poor". I'd also like to be able to use filtering for course date, trainer etc.
I've exported the data from both the Access tables to Excel, and now I'm trying to use the Excel data model and pivot tables/charts to create a pivot chart that allows me to summarise feedback ratings across the 'questions' similar to this: Desired result / stacked bar
I can't seem to set up the relationships correctly in excel, to get the 'count' function working across the 'questions' to return the right counts for each Feeback rating / question. It seems to only want to set up one relationship between these tables, and only one questions returns the counts correctly.
Any ideas on how i can set this up? Perhaps a new measure?
Many thanks in advance.