Obtaina final grade what if scenario

adulted

Active Member
Joined
Jan 22, 2004
Messages
339
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.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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

Active Member
Joined
Jan 22, 2004
Messages
339
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

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS

ADVERTISEMENT

Hi:

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


plettieri
 

adulted

Active Member
Joined
Jan 22, 2004
Messages
339
Hello plettieri

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

Paul
 

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS

ADVERTISEMENT

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
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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.
 

adulted

Active Member
Joined
Jan 22, 2004
Messages
339
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

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
Hi:

Try


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


then format results 2 decimal places...
plettieri
 

Watch MrExcel Video

Forum statistics

Threads
1,123,089
Messages
5,599,667
Members
414,326
Latest member
Aerith

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top