Calculate percent change of first and last cell in a range

[smd1112]

Hello, any help is appreciated. Basically, I have a list of students that have to take two different tests (a reading and a math) at 5 specific times per year. I have to set up a spreadsheet that accomplishes two things:
-Calculate percent change for each student between each administration of the tests (which I have done), AND
-Calculate percent change for each student between the first and last administration of the tests

This would be easy, except not all students will take the tests every time. Many students transfer in and out at different times throughout the year, so they might only have scores for the last 3 columns.

How would I make excel look for the first and last cells that have data in columns F-J and calculate percent change from the first score to the last in column D. Same thing for columns T-W and calculate percent change in column E? MIN MAX isn't ideal because some students scores are erratic, not steady up or down.

I can't attach a file, otherwise I would attach what I am working with. Thanks for any advice.

 

[bbott]

Assuming scores in row 1, try:

=(INDEX(F1:J1,1,COUNT(F1:J1))-F1)/F1

 

[ExcelMercy]

Here is how I put the data:

Excel 2010

	A	B	C	D
1	Student Name	Reading Score	Math Score	Test #
2	Billy	78	35	1
3	Jon	59	90	1
4	Jenny			1
5	Chris	80	70	1
6	Sarah			1
7	Billy	76	65	2
8	Jon	78	79	2
9	Jenny	59	86	2
10	Chris	73	94	2
11	Sarah			2
12	Billy	56	98	3
13	Jon	59	57	3
14	Jenny	79	45	3
15	Chris	45	70	3
16	Sarah			3
17	Billy	29	69	4
18	Jon	60	84	4
19	Jenny	88	97	4
20	Chris			4
21	Sarah	78	84	4
22	Billy	45	42	5
23	Jon	59	90	5
24	Jenny	54	100	5
25	Chris			5
26	Sarah	92	32	5

<tbody>
</tbody>

Sheet3​

For the comparisons between each individual test:

Example: Cell: G2
=IF(OR(B7="",B2=""),"Missing Test", (B7/B2)-1)


Excel 2010

	F	G	H
1	Student Name	Reading Test 1 to 2	Math Test 1 to 2
2	Billy	-2.6%	85.7%
3	Jon	32.2%	-12.2%
4	Jenny	Missing Test	Missing Test
5	Chris	-8.8%	34.3%
6	Sarah	Missing Test	Missing Test
7
8	Student Name	Reading Test 2 to 3	Math Test 2 to 3
9	Billy	-26%	51%
10	Jon	-24.4%	-27.8%
11	Jenny	34%	-48%
12	Chris	-38.4%	-25.5%
13	Sarah	Missing Test	Missing Test
14
15	Student Name	Reading Test 3 to 4	Math Test 3 to 4
16	Billy	-48%	-30%
17	Jon	2%	47%
18	Jenny	11%	116%
19	Chris	Missing Test	Missing Test
20	Sarah	Missing Test	Missing Test
21
22	Student Name	Reading Test 4 to 5	Math Test 4 to 5
23	Billy	55%	-39%
24	Jon	-2%	7%
25	Jenny	-39%	3%
26	Chris	Missing Test	Missing Test
27	Sarah	18%	-62%

<tbody>
</tbody>

Sheet3​

For the comparison between the first test taken and the last test.Example: Cell: K2
=IF(NOT(ISBLANK(B2)),B2,IF(NOT(ISBLANK(B7)),B7,IF(NOT(ISBLANK(B12)),B12,IF(NOT(ISBLANK(B17)),B17,IF(NOT(ISBLANK(B22)),B22,"None")))))

% Change.
Example: Cell M2
=(K2/L2)-1



Excel 2010

	J	K	L	M
1	Student Name	1st Reading Test	Last Reading Test	% Change
2	Billy	78	45	73%
3	Jon	59	59	0%
4	Jenny	59	54	9%
5	Chris	80	45	78%
6	Sarah	78	92	-15%
7
8	Student Name	1st Math Test	Last Math Test	% Change
9	Billy	35	42	-17%
10	Jon	90	90	0%
11	Jenny	86	100	-14%
12	Chris	70	70	0%
13	Sarah	84	32	163%

<tbody>
</tbody>

Sheet3​

 

[spacebouncer]

Try using this. Forgive me if I'm telling you to suck eggs, but you'll need to copy and paste into Visual Basic. You can click on the View menu, Macros, View Macros. Create a new macro, and then copy and paste what's below. See what parts of it you can work out, and change as required.

Sub mypercentages()

Dim allpercentages As Range, percentagecell As Range, _
startcell As Range, endcell As Range, first As Range, last As Range
Set allpercentages = ActiveSheet.Range("D2:D10")    'Set your range here

For Each percentagecell In allpercentages
    Set startcell = percentagecell.Offset(0, 2)     'Change the offset to work with different columns
    Set endcell = percentagecell.Offset(0, 6)       'Change the offset to work with different columns
    Set first = Nothing
    Set last = Nothing
    For Each cell In Range(startcell, endcell)
        If cell.Value > 0 Then
            If first Is Nothing Then
                Set first = cell
            End If
            Set last = cell
        End If
    Next cell
    If Not first Is Nothing Or Not last Is Nothing Then
        percentagecell.Value = ((last.Value - first.Value) / first.Value) * 100
    Else
        percentagecell.Value = "Insufficient Data"      'Change tag as appropriate
    End If
Next percentagecell

End Sub

 

[smd1112]

Thanks for all the suggestions! I ended up vba'ing spacebouncer's code in order to accomplish what I needed through the use of two command buttons that would run the code (one for the reading and one for the math). I forgot to mention that I would ultimately be mail merging the scores into a word doc to create reports to send to the kids' parents, so having all scores on single rows was necessary also. Otherwise, all the suggestions would have worked. Again, you guys are awesome! Thanks so much for your help.

 

[spacebouncer]

Teachers have enough to do ay!

 

[smd1112]

They really do. It's getting harder and harder. Thanks again. I really appreciate it.

 

[smd1112]

Where did you guys learn vba? In a class? Books? I am trying to pick it up but it's slow going.

 

[spacebouncer]

I'm a Maths teacher. I'm learning because I have a program in mind I'd like to make. The ideas evolved hugely from basic things I started doing in Excel. So it started with very slow trial and error, recording macros, figuring out how the code worked. Then I learnt some programming basics.

Start with the basics, types of data, variables, and progress to functions, objects, arrays etc.

There's loads of stuff online. Particularly for other languages like Javascript. There's obviously a lot of differences but the principles are the same, and you can figure out how things translate accross without much problem. Codeacademy is quite good. I joined Treehouse. Its pay to use, but a reasonable price and they have a good format. I didn't shop around too much though.