How to analyse results from survey completed twice over time

Hello,

I work for a charity that supports neurodiverse young people. We're going to begin taking wellbeing surveys with the young people at the start of their case and the end to attempt to measure our impact. There are 8 questions on the form and they are all looking for an answer on a scale of 1-10 with 10 being the best. These questions will be the same at all times, for all the young people.

We'll use MS Forms to take the surveys and a case reference number to identify who it is. I thought this would be easy enough to do but I can only see how to get it done for 1 complete set of surveys at a time. How can I use Excel to analyse the change in each question over time from all the young people but only include those that have answered it twice?

So for Q1 if ChildA answers 3/10 on 1/4/22 and then when they close the case at say 10/7/22 they answer 5/10, I can show that improvement in a chart, call it +2 or 67% etc. How can I do this across the board to show the average change in everyone who has responded twice?

Apologies if I've not explained that well! I feel like this is quite an obvious thing that'd be done on surveys so I'm surprised I've not been able to find the answers on Google. There was one that was like a staff survey done over different years but this seems more complicated as the dates will all be varied.

Any help appreciated, thanks.

Ah, apologies I've now added this and some extra rows. The unique reference would be the individual so once there are 2 of the same value we'd want to compare their answers from their first entry to the 2nd. But there will end up being many individual entries like 10B (row 16) with just one entry while their case is open.

Hi there,

I think you may be over complicating your analysis... are you wanting to see the change from each individual question, or as an overall? To be fair, MS forms will do a lot of work for you when you finish and you can export the results to an excel workbook. I am happy to help out via PM if you want, this is stuff I do on a day to day basis and your cause is close to my heart. Regards

Rob

Thanks Dive, yeah I think I've definitely overcomplicated it and it does appear that we can possibly set this up directly within our Dynamics CRM so an Excel sheet may not be required after all.

I got a bit further by changing it to 2 forms, one to complete at the start and another, the same form, to complete at the end. I then used the results from the 2nd form in Excel and VLookup to find what was answered on the first form and then created a column to show the difference and put this into a slope chart.

I'm sure there's probably a way to do this from the single table the form would generate but this will do the trick should we not use Dynamics.

Thanks again for the offer though, appreciated