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.