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.

 

[plettieri]

Hi:

I have read you questions several times and still a bit confusing ...Might others and me to post sample data sheet with your data and expected outcome desired.

You can find the Colo utility located below this tesxt box

plettieri

 

[Yogi Anand]

Hi Paul:

As plettieri has stated it will be nice to get a clearer picture of what you are trying to accomplish.

However from what I have iunderstood, let me take a shot at it ...

Formula in cell P9 ... =SUM($L9:$O9)/SUMPRODUCT(($L$4:$O$4))

Is this what you are looking for?

 

[adulted]

Re: Obtain a final grade what if scenario

I am not able to get Colo's HTML to work here. At any rate, let me simplify the question.

We'll use 1 student, and I'll give an example of a proper grade we require.

A4 Possible Marks
L4 20
M4 20

A7 Student 1
L7 15
M7 12

The above student would think thier mark is only 27 out of 40.

To get this mark translated out of 100, we would normally perform the following formula in P7

=SUM(L7:M7)*100/40
Returning the result of 68

I don't want to have to compare the possible marks with what each student has chosen, and modify each row's formula from P7:P18


The formula sent =SUM($L9:$O9)/SUMPRODUCT(($L$4:$O$4)) returned 0

It appears it was referring to the following data on my spreadsheet.

Possible Marks:
L4 20
M4 20
N4 25
O4 25

Student marks
L9 19
M9 19
N9
O9

This mark should have returned 95 using my formula:
=SUM(L9:M9)*100/40

Hope this helps. I'll read up on Colo's Html.

Paul

 

[plettieri]

Hi:

So, you only want to score on the tests that the student has taken, right?


plettieri

 

[adulted]

Hello plettieri

Yes, I wish only to score on the exams taken from the chosen subjects.

Paul

 

[plettieri]

Hi:

Bump...

pulling my hair ...can't remember here...


I think this is what is needed...

Book1
	A	B	C	D	E	F	G
1	possible	actual
2	10	8
3	10	8
4	100	75
5	100
6	100
7
8
9	Expression needed		91 divided by 120= 75.8%
10
11
12	Only select where a and b columns when amounts in col b >0 amounts
13
14
15
Sheet1

any assistance from those listening?

plettieri

 

[Yogi Anand]

Hi Paul:

If I understand you correctly, have a look at ...

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

Is this what you are looking for? If I have misunderstood your question -- please explain a little further -- and then let us take it from there.

 

[adulted]

Hello Yogi Anand, and plettieri

Thank you's so very much for your hard work.
The formula =SUM(L9:O9)/(SUMIF(L9:O9,"<>",L4:M4)) did indeed work, I've modified it to accomodate my spreadsheet. As you know the possible marks are on Row 4, and my first students marks begin on row 7, though row 18, listing for 12 students. The modified formula now reads: formatted as percentage.
=SUM(B7:O7)/(SUMIF(B7:O7,"<>",$B$4:$O$4)) copied down to row 18.

I gave only an example of columns L to O, as not to have it look as complicated as it is.

The only issue my boss had brought up, is there a way rather than format the cells as percentage, can we not have a mark of ie: 89.56

Your help has been greatly appreciated.

Paul

 

[plettieri]

Hi:

Try


=SUM(L9:O9)/(SUMIF(L9:O9,"<>",L4:M4))*100


then format results 2 decimal places...
plettieri