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

	GRADE 7-QUARTER 2
READING AND INTERPRETING INFORMATIONAL TEXT

<tbody>
</tbody>

A B C D E F G H I J K L

	NAME	TEST 1	TEST 2	TEST 3	TEST 4	TEST 5	TEST 6	% 1:2	% 1:3	% 1:4	% 1:5	% 1:6
7	A1	24	84	90				60
8	A2	36	80	80				44
9	A3	62	84	90				22
10	A4	48	80	80				32
11	A5	76	80	52				4
12	A6	0	88	90				88
13	A7	48	76	55				28
14	A8	20	44	56				24
15	A9	32	56	64	72			24
16	A10	0	66					66
17	A11	92	76					-16
18	A12	48	64	80				16
19	A13	32						-32
20	A14	82	76					-6
21	A15	64	76					12
22	A16	72						-72
23	A17	28	68	76				40
24	A18	52						-52
25	A19	0	45					45
26	A20	28	80	80				52
27	A21	88	68	90				-20
28	A22	52	80					28
29	A23	36	84	90				48
30	A24	0	70	80				70
31	A25	20	76					56
32	A26	20	100					80
33	A27	40	92					52
34	B1	56	100	96				44
35	B2	60	100	100				40
36	B3	56	80					24
37	B4	56	84					28
38	B5	64	88	88	68			24
38	B6	32	60					28
40	B7	68	90					22
41	B8	40	96	96	100			56
42	B9	44	100	92	80			56
43	B10	12	92	76				80
44	B11	24	72	64	68			48
45	B12	68	80	76				12
46	B13	64	100	95				36
47	B14	56	96					40
48	B15	60	88	88	88			28
49

<tbody>
</tbody>

 

[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.