excel survey calcustion, pre and post session.

[Iain McBride]

A colleague has asked to see if there a way of creating a formula to calculate the progression that a group of young people have made through a survey folllowing a short course.
The table below shows 9 young people and where they gauged their level of understanding before and after the session (1 being not at all and 4 completely understand)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]pre[/TD]
[TD]post[/TD]
[TD]pre[/TD]
[TD]post[/TD]
[TD]pre[/TD]
[TD]post[/TD]
[TD]pre[/TD]
[TD]post[/TD]
[TD]progression[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Is there a formula that will calculate the difference that each student has made (ie post-pre = progression)?

Thanks
Iain

 

[Peter_SSs]

Always a good idea to provide the expected results and an explanation of how you would get them manually. As it is, we are left to guess both. This is my guess.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1			1		2		3		4
2		pre	post	pre	post	pre	post	pre	post	progression
3	1							1	1	0
4	2			1			1			1
5	3			1			1			1
6	4	1					1			2
7	5			1			1			1
8	6				1	1				-1
9	7	1					1			2
10	8				1	1				-1
11	9	1			1					1
Progress

 

[Iain McBride]

Thanks Peter - you're a true star!!!

 

[Peter_SSs]

Thanks Peter

You're welcome. Thanks for the follow-up.