# Calculate percent change of first and last cell in a range

#### smd1112

##### New Member
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.

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Assuming scores in row 1, try:

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

Here is how I put the data:

Excel 2010
ABCD
2Billy78351
3Jon59901
4Jenny1
5Chris80701
6Sarah1
7Billy76652
8Jon78792
9Jenny59862
10Chris73942
11Sarah2
12Billy56983
13Jon59573
14Jenny79453
15Chris45703
16Sarah3
17Billy29694
18Jon60844
19Jenny88974
20Chris4
21Sarah78844
22Billy45425
23Jon59905
24Jenny541005
25Chris5
26Sarah92325

<tbody>
</tbody>
Sheet3

For the comparisons between each individual test:

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

Excel 2010
FGH
1Student NameReading Test 1 to 2Math Test 1 to 2
2Billy-2.6%85.7%
3Jon32.2%-12.2%
4JennyMissing TestMissing Test
5Chris-8.8%34.3%
6SarahMissing TestMissing Test
7
8Student NameReading Test 2 to 3Math Test 2 to 3
9Billy-26%51%
10Jon-24.4%-27.8%
11Jenny34%-48%
12Chris-38.4%-25.5%
13SarahMissing TestMissing Test
14
15Student NameReading Test 3 to 4Math Test 3 to 4
16Billy-48%-30%
17Jon2%47%
18Jenny11%116%
19ChrisMissing TestMissing Test
20SarahMissing TestMissing Test
21
22Student NameReading Test 4 to 5Math Test 4 to 5
23Billy55%-39%
24Jon-2%7%
25Jenny-39%3%
26ChrisMissing TestMissing Test
27Sarah18%-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
JKLM
2Billy784573%
3Jon59590%
4Jenny59549%
5Chris804578%
6Sarah7892-15%
7
8Student Name1st Math TestLast Math Test% Change
9Billy3542-17%
10Jon90900%
11Jenny86100-14%
12Chris70700%
13Sarah8432163%

<tbody>
</tbody>
Sheet3

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.

Code:
``````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``````

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.

Teachers have enough to do ay!

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

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

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.

Replies
13
Views
380
Replies
1
Views
329
Replies
3
Views
591
Replies
7
Views
142
Replies
23
Views
724

1,214,499
Messages
6,119,895
Members
448,929
Latest member
Giovannicavuccio

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back