Calculate percent change of first and last cell in a range

smd1112

New Member
Joined
Sep 9, 2014
Messages
4
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.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

bbott

Well-known Member
Joined
Feb 5, 2010
Messages
2,350
Assuming scores in row 1, try:

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

ExcelMercy

Board Regular
Joined
Aug 11, 2014
Messages
151
Here is how I put the data:

Excel 2010
ABCD
1Student NameReading ScoreMath ScoreTest #
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
1Student Name1st Reading TestLast Reading Test% Change
2Billy784573%
3Jon59590%
4Jenny59549%
5Chris804578%
6Sarah7892-15%
7
8Student Name1st Math TestLast Math Test% Change
9Billy3542-17%
10Jon90900%
11Jenny86100-14%
12Chris70700%
13Sarah8432163%

<tbody>
</tbody>
Sheet3









 

spacebouncer

Board Regular
Joined
Feb 7, 2014
Messages
109
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
 

smd1112

New Member
Joined
Sep 9, 2014
Messages
4

ADVERTISEMENT

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.
 

smd1112

New Member
Joined
Sep 9, 2014
Messages
4

ADVERTISEMENT

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

smd1112

New Member
Joined
Sep 9, 2014
Messages
4
Where did you guys learn vba? In a class? Books? I am trying to pick it up but it's slow going.
 

spacebouncer

Board Regular
Joined
Feb 7, 2014
Messages
109
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,054
Members
414,357
Latest member
Gemma_R

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top