Help-new user need excel formula?

[parel09]

HELP-NEW USER NEED EXCEL FORMULA?
The students have been labeled a1, a2 etc..
Their grades are in b, c d e etc..
In column h the percentage change between test 1 and test 2, grades are already in percentage so normally c7-b7 is the percentage
Problem: in A 13 for example excel assumes a zero for test 2, there was only one test given therefore in h13 the percentage change should be 0—in need of a formula to reflect this throughout the workpage. Note: WHERE THERE ARE ZEROS IN THE TEST 1,2 ETC.. THAT IS THE ACTUAL GRADE
[TABLE="width: 424"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 3"] GRADE 7-QUARTER 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]READING AND INTERPRETING INFORMATIONAL TEXT
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
A B C D E F G H I J K L
[TABLE="width: 710"]
<tbody>[TR]
[TD]
[/TD]
[TD]NAME
[/TD]
[TD]TEST 1
[/TD]
[TD]TEST 2
[/TD]
[TD]TEST 3
[/TD]
[TD]TEST 4
[/TD]
[TD]TEST 5
[/TD]
[TD]TEST 6
[/TD]
[TD]% 1:2
[/TD]
[TD]% 1:3
[/TD]
[TD]% 1:4
[/TD]
[TD]% 1:5
[/TD]
[TD]% 1:6
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]A1
[/TD]
[TD]24
[/TD]
[TD]84
[/TD]
[TD]90
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]60
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]A2
[/TD]
[TD]36
[/TD]
[TD]80
[/TD]
[TD]80
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]44
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]A3
[/TD]
[TD]62
[/TD]
[TD]84
[/TD]
[TD]90
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]22
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]A4
[/TD]
[TD]48
[/TD]
[TD]80
[/TD]
[TD]80
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]32
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]A5
[/TD]
[TD]76
[/TD]
[TD]80
[/TD]
[TD]52
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]4
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]A6
[/TD]
[TD]0
[/TD]
[TD]88
[/TD]
[TD]90
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]88
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]A7
[/TD]
[TD]48
[/TD]
[TD]76
[/TD]
[TD]55
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]28
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]A8
[/TD]
[TD]20
[/TD]
[TD]44
[/TD]
[TD]56
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]24
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]A9
[/TD]
[TD]32
[/TD]
[TD]56
[/TD]
[TD]64
[/TD]
[TD]72
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]24
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]A10
[/TD]
[TD]0
[/TD]
[TD]66
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]66
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]A11
[/TD]
[TD]92
[/TD]
[TD]76
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]-16
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]A12
[/TD]
[TD]48
[/TD]
[TD]64
[/TD]
[TD]80
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]16
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]A13
[/TD]
[TD]32
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]-32
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]A14
[/TD]
[TD]82
[/TD]
[TD]76
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]-6
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD]A15
[/TD]
[TD]64
[/TD]
[TD]76
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]12
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]A16
[/TD]
[TD]72
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]-72
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD]A17
[/TD]
[TD]28
[/TD]
[TD]68
[/TD]
[TD]76
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]40
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]24
[/TD]
[TD]A18
[/TD]
[TD]52
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]-52
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]25
[/TD]
[TD]A19
[/TD]
[TD]0
[/TD]
[TD]45
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]45
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]26
[/TD]
[TD]A20
[/TD]
[TD]28
[/TD]
[TD]80
[/TD]
[TD]80
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]52
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]27
[/TD]
[TD]A21
[/TD]
[TD]88
[/TD]
[TD]68
[/TD]
[TD]90
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]-20
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]28
[/TD]
[TD]A22
[/TD]
[TD]52
[/TD]
[TD]80
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]28
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]29
[/TD]
[TD]A23
[/TD]
[TD]36
[/TD]
[TD]84
[/TD]
[TD]90
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]48
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]30
[/TD]
[TD]A24
[/TD]
[TD]0
[/TD]
[TD]70
[/TD]
[TD]80
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]70
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]31
[/TD]
[TD]A25
[/TD]
[TD]20
[/TD]
[TD]76
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]56
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]32
[/TD]
[TD]A26
[/TD]
[TD]20
[/TD]
[TD]100
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]80
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]33
[/TD]
[TD]A27
[/TD]
[TD]40
[/TD]
[TD]92
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]52
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]34
[/TD]
[TD]B1
[/TD]
[TD]56
[/TD]
[TD]100
[/TD]
[TD]96
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]44
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]35
[/TD]
[TD]B2
[/TD]
[TD]60
[/TD]
[TD]100
[/TD]
[TD]100
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]40
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]36
[/TD]
[TD]B3
[/TD]
[TD]56
[/TD]
[TD]80
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]24
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]37
[/TD]
[TD]B4
[/TD]
[TD]56
[/TD]
[TD]84
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]28
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]38
[/TD]
[TD]B5
[/TD]
[TD]64
[/TD]
[TD]88
[/TD]
[TD]88
[/TD]
[TD]68
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]24
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]38
[/TD]
[TD]B6
[/TD]
[TD]32
[/TD]
[TD]60
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]28
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]40
[/TD]
[TD]B7
[/TD]
[TD]68
[/TD]
[TD]90
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]22
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]41
[/TD]
[TD]B8
[/TD]
[TD]40
[/TD]
[TD]96
[/TD]
[TD]96
[/TD]
[TD]100
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]56
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]42
[/TD]
[TD]B9
[/TD]
[TD]44
[/TD]
[TD]100
[/TD]
[TD]92
[/TD]
[TD]80
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]56
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]43
[/TD]
[TD]B10
[/TD]
[TD]12
[/TD]
[TD]92
[/TD]
[TD]76
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]80
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]44
[/TD]
[TD]B11
[/TD]
[TD]24
[/TD]
[TD]72
[/TD]
[TD]64
[/TD]
[TD]68
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]48
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]45
[/TD]
[TD]B12
[/TD]
[TD]68
[/TD]
[TD]80
[/TD]
[TD]76
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]12
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]46
[/TD]
[TD]B13
[/TD]
[TD]64
[/TD]
[TD]100
[/TD]
[TD]95
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]36
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]47
[/TD]
[TD]B14
[/TD]
[TD]56
[/TD]
[TD]96
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]40
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]48
[/TD]
[TD]B15
[/TD]
[TD]60
[/TD]
[TD]88
[/TD]
[TD]88
[/TD]
[TD]88
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]28
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]49
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[alansidman]

Assuming test 2 score is in column C. Try this and copy down.

=if(c7="",B7,C7-B7)

 

[parel09]

you are correct that b,c,d are all test scores, when i use the formula you gave me, it simply duplicates the first test score. for example in b and c 19, the only score was the first test, a score of 32 percent. no test taken for the second test therefore there is no change in the percent difference between the tests. test two is not a zero. h19 cell should either be blank or reflect 0 as there was no percentage change in the test

thanks i appreciate your efforts

 

[parel09]

you are correct the formula works when i change the b7 in the center to read h7 which is where i need the percentage of change to score reflected--thanks--unfortunately not able to use the same manner for next column looking for the difference between d and b column--go figure

i need my formula to say:assuming i were in row of 7 for 13 for example:

if a cell is empty (c13 or b13) then enter 0 in h13, if not empty then c13-b13

 

[alansidman]

What happens beyond column C if there is an empty cell. You will need to nest if statements along with the OR function.

Look at this link on nested If statements and then at the OR function

MS Excel: Nested IF Functions (WS)

MS Excel: OR Function (WS)

Working these together will allow you to get where you need to be.

 

[Marcelo Branco]

Try this in H7

=IF(C7<>"",C7-B7,"")

copy across till L7 and down

M.