# Obtaina final grade what if scenario

##### Active Member
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

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.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
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

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?

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

Hi:

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

plettieri

Hello plettieri

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

Paul

Hi:

Bump...

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

I think this is what is needed...
Book1
ABCDEFG
1possibleactual
2108
3108
410075
5100
6100
7
8
9Expression needed91 divided by 120= 75.8%
10
11
12Only select where a and b columns when amounts in col b >0 amounts
13
14
15
Sheet1

any assistance from those listening?

plettieri

Hi Paul:

If I understand you correctly, have a look at ...
y040209h1a.xls
LMNOPQ
420202525
5
6
7
8
919190.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.

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

Hi:

Try

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

then format results 2 decimal places...
plettieri

Replies
10
Views
369
Replies
1
Views
158
Replies
2
Views
188
Replies
8
Views
152
Replies
0
Views
259

1,203,485
Messages
6,055,691
Members
444,807
Latest member
RustyExcel

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

### Which adblocker are you using?

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

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