Analyse survey results completed twice over time of support

[Chris Nicol]

Hello,

I have an MSForm with 8 questions about wellbeing where every answer will be from 1-10. I work for a charity and we want to complete this form with a young person when they start working with us and again when they stop. We're then hoping that we can measure successful results.

I can use the filters to compare and measure the difference between an individual's survey responses but how can I do this across the board to find the average of the changes?

I have one extra question on the form to add an anonymous indicator of who it is so that when they complete the survey for a 2nd time I can match it to their first. But there will also be many rows of data where we just have the first survey while we are still supporting them?

I thought this would be straight forward but I'm totally stumped and I'm struggling to find anything online beyond year on year comparisons.

I've included the excel sheet with 15 rows of dummy data.

Any help appreciated, thanks.

[#2]

@Chris Nicol 

Well, for one thing, to do a year over year comparison you would have to have data from two years (or more). I've added some rows of random data, and changed the field to just be a date. (Do you really need the time of start and the time of completion? If so, fine, but I'd make it simpler to summarize by also have a field that is a simple date)

Then the Pivot Table does a good job of summarizing. I've given one example, but there are all kinds of ways to use a Pivot Table with a single set of data. Here's a reference you might find useful. There are also many MANY instructional videos on YouTube.

[#3]

@mathetes Thanks for the reply but I mustn't have been clear with my OP.

I'm not looking to compare year on year but rather start to end so it could be 1 year or it could be 1 month. I've made progress with this but feel like there must be a better way to do it. The start time and completion time come with the Form.

I've made it easier for myself by having it as 2 different MS forms to separate the data and on Page 2 (the results from the end of case form) I can find the results only for those that have completed both forms and show the change and then the total average of all forms that have been completed twice to, hopefully, show that improvement in wellbeing. 

On the charts tab I've created the slope charts but I realise this includes those that have only completed the first form so isn't as accurate as I'd like.

I've just realised I can add this into changes part of the table on the End tab too and then show the change in 2 columns rather than the 1 VLookup with the sum.  Again though, it feels like there's probably a better way.

[#4]

@Chris Nicol 

I'm not looking to compare year on year but rather start to end so it could be 1 year or it could be 1 month.

If I'm understanding your "Unique Reference" comparisons correctly, to take just one example, 2CN, you're talking about a first test at 1:25 p.m. on 9/29/22 as compared with a second completion approximately TWO HOURS later, certainly not a year, but not even close to a month. I.e., a meaningless gap.

That's what confuses me......is the data not real? 

For a normal Pivot Table, you want all of the data to be in a single table. You then want to have a single column in that table that differentiates instances. Power Query and Power Pivot Tables (I have no experience with either of them)  make it possible to combine separate tables into a single Pivot or single query. But for such things as the Pivot Table, you don't need to do that.

I've just realised I can add this into changes part of the table on the End tab too and then show the change in 2 columns rather than the 1 VLookup with the sum.  Again though, it feels like there's probably a better way.

One of the delightful things about Excel (and maybe it's a frustrating thing too, at times) is that there are always going to be multiple ways to get from point A to point B, different functions that can be used, different graphs to illustrate results. My sense (from what you've written as well as from the spreadsheet you've shared) is that you are the best one to address your need. You clearly are willing to play around with the data (in a healthy way), to structure it this way and that, to see whether or not this way is clearer than that. In all candor, that's what I'd have to do too.

I was involved in a tele-conversation just last week with some of the other regular posters [those who answer questions here], and we agreed that sometimes we need to just encourage people--like you--who are asking questions in this forum, ...encourage you to just play around, experiment. There truly isn't "one best way" to go from your raw data to a clear conclusion. There are multiple ways...if I were sensing in you a person who didn't know how to write a formula, it'd be a different case, but you are clearly comfortable with VLOOKUP, tables,  line charts. 

If I were to give you a suggestion (just to make your formulas easier to read), it would be to use some simpler headings (keep the wordy ones for final reports) in the tables and formulas. Maybe become familiar with named ranges, the relatively new LET function, maybe FILTER. I've mentioned Pivot Tables, and if you're not familiar with them, you should be. But mostly I'd encourage you to keep going and learn on your own. Come here with specific questions if you can't make a function work, or don't know what function you need to accomplish a given purpose; your question about the best way to analyze YOUR data, though, is a question that you are best qualified to answer.