Obtaina final grade what if scenario

[adulted]

Where do I start to explain?

I have several students taking several subjects with a choice of a few specialty subjects. In saying this, not all students would have to take the two assignments for Access and Outlook, or possibly others. Possibly on the chosen specialty, only Access is required. The initial formaula is as follows:


Access Possible Marks

Assign 1: 20
Assign 2: 20
Total 40

Access Marks acheived:
Assign 1: 15
Assign 2: 12
Total 27

To have this show out of a 100 % I formulate as follows:

=15+12*100/40 = 67.5 / 100

Here is a sample of a spreadsheet:

A4 Possible Marks
L4 20
M4 20
N4 25
O4 25
P4 =SUM(L4:O4)

(Merged) L5:M6 Access
(Merged) N5:O5 Outlook
P5 Final Grade

L6 Assign 1
M6 Assign 2
N6 Assign 1
O6 Assign 2

A7 Student 1
A8 Student 2
A9 Student 3
A10 Student4
A11 Student5

L7 15
M7 12
N7
O7
P7 {=SUM(IF(ISNUMBER(L7:O7),L7:O7))} ctrl+shift+enter

L8 10
M8 10
N8 20
O8 20
P8 {=SUM(IF(ISNUMBER(L8:O8),L8:O8))} ctrl+shift+enter

L9 15
M9 15
N9
O9
P9 =SUM(L9:O9)*100/$P$4

Below:
This worls, although it is based on the absolute cell which expects that all asssignments were taken.

L10 10
M10 10
N10 20
O10 20
P10 =SUM(L10:O10)*100/$P$4

Below:
This works, except I do not want to modify formulas, based on the division of what the assignments were out of, baseed on what they took or not.

L11 15
M11 15
N11
O11
P11 =SUM(B11:O11)*100/40

Paul
How many hairs were on my head? at least there countable now.

 

[Yogi Anand]

Hi adulted:

In addition to fine contribution from plettieri, if you don't want to have to format the resulting cell, then use ...

y040209h1a.xls
	L	M	N	O	P	Q
4	20	20	25	25
5
6
7
8
9	19	19				95.00
Sheet1

I hope this helps!