0

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.

  • Maybe it’s my fault.  Maybe I forgot to take my “smart” pill today.  But I have no idea what data you have, how they interrelate, what results you want, and what problem(s) you are having. Please do not respond in comments; edit your question to make it clearer and more complete. – Scott Oct 30 '18 at 2:47
  • Thankyou @Scott Apologies for not explaining clearly. I've attempted to clarify but admit that i find it difficult to know how to explain – Excel Noob Oct 30 '18 at 6:01
  • 1
    " I've exported the data from both the Access tables to Excel"[done] > "I can't seem to set up the relationships correctly in excel"[ok..] > "to return the right counts for each Feeback rating / question"[noted..] ... " It seems to only want to set up one relationship" [ which one? ] ... "only one questions returns the counts correctly. "[ which one? (again) ] .. "Any ideas on how i can set this up? Perhaps a new measure? "[ so the old measure is ... ] || That's my head thinking out loud.. or at least my heart speaking above it.. I think you should share the sample data.. so that we can ... – p._phidot_ Oct 30 '18 at 7:21
  • 1
    ... 'see' the column/ question/ 'which one' / 'measure' / and the original 'idea' you meant. || Walk us again with the shared file / table / formula .. And I think It'll be better.. you may share your file using google drive or one drive. /(^_^) – p._phidot_ Oct 30 '18 at 7:24
  • @ExcelNoob,, rating part can be performed in Access also ,, create Query and use Calculated fields,, there also you can try If Else,, Count and other aggregate functions !! – Rajesh S Oct 30 '18 at 8:33
0

I'm not familiar with the access link to excel.. thus, I'd manually included the data in the excel, > do a countif.. > then convert to % > select table and press F11 to create 100% staked bar chart.

more info:

Rawdata - A1:L131

CountTable - N1:S14 with this formula at its core : =COUNTIF(OFFSET($A$2:$L$131,0,ROW()-3,ROWS($A$2:$L$131),1),O$1)

%_CountTable - N18:S30 with this formula at its core : =O3/$U3 , where U3:U14 is the sum of response for each question.

If you select N18:S30 , then press F11, then goto 'design', 'chg chart type', 'bar', then select '100% staked bar'. The resultant chart (Chart3) seems to show question 12 1st.. (instead of Question 1 1st).

So, as a workaround, I've reversed the %_CountTable. to generate the N32:S44 table. then only select it, > F11 > Select '100% staked bar' . I get Chart4 (as intended).

Note : Choose chart style 4 to display the % || Also in the %_CountTable, I've formatted the numbers as "0%" so that it display the percentage correctly.

Hope it helps.

  • What you've outlined in your example file is perfect. Thank you so much for your time in helping. I'm replicating this method across a few courses and this will speed up my quarterly work a lot. I'll abandon the idea of using calculated measures and pivots. your solution is much more straight forward and elegant. Thankyou – Excel Noob Nov 21 '18 at 5:26
  • You're welcomed. ( : – p._phidot_ Nov 21 '18 at 7:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.