A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Function help please.



 
 
Thread Tools Display Modes
  #1  
Old June 3rd, 2010, 01:53 PM posted to microsoft.public.excel.worksheet.functions
Helios150
external usenet poster
 
Posts: 1
Default Function help please.

I am a teacher and have an excel spreadsheet (2003) with student's marks in
various columns. Each column represents a quiz/exam/OP or assignment and they
are all out of different totals. I need to know how to:
1) reduce each column to it's percentage value (example to follow) and,

2) to sum up totals from each row into a percentage final mark.

Assume we are using A1 as the first 5% piece of information, so the marks in
the columns would go from: A4:A34, B4:B34, etc

Thanks in advance,

Andrew

5% 5% 15% 30% 30% 15%
26 marks 20 marks 40 marks 60 marks 55 marks
Quiz 1 Quiz 2 Mid-Term Presentation Special Exam O/P
9 9.5 33.0 49.0
23 14.5 37.0 48.0
16 9 33.0 47.0
10 9.5 32.0 51.0
7 9 18.0 10.0
14 12.5 31.0 53.0
16 15 37.0 53.0
19 8.5 40.0 49.0
17 12 40.0 54.0
13 13 31.0 43.0
15 9 30.0 53.0
22 9 25.0 55.0
16 20 38.0 59.0
18 12.5 27.0 53.0
15 11.5 40.0 49.0
16 14.5 33.0 53.0
14 10.5 37.0 53.0
11 4 24.0 48.0
11 11 33.0 51.0
17 12 34.0 51.0
21 9.5 30.0 59.0
26 17.5 34.0 48.0
16 10.5 35.0 51.0
10 10.5 18.0 54.0
14 16.5 39.0 54.0
12 11 33.0 41.0
10 7 22.0 31.0
22 20 40.0 59.0
7 4.5 21.0 49.0
20 17 32.0 54.0
23 13.5 37.0 53.0

  #2  
Old June 3rd, 2010, 05:09 PM posted to microsoft.public.excel.worksheet.functions
Pete_UK
external usenet poster
 
Posts: 8,780
Default Function help please.

So, the first student scores 9 out of 26 marks for Quiz 1, and this
contributes 5% of the overall mark?

If you want to know the percentage score in each category (i.e. 9 out
of 26), then replace the second row with just numbers (i.e. 26 instead
of 26 marks, etc) and put this formula in (for example) I4:

=A4/A$2

Format the cell as a percentage, and you can copy this across and down
to suit the number of categories and the number of students. You will
get these four results for the first two students from your example
data:

34.6% 47.5% 82.5% 81.7%
88.5% 72.5% 92.5% 80.0%

Then in P4, for example, you can convert this to the overall
contribution with this formula:

=I4*A$1

and again this can be copied across and down to suit, to give you
this:

1.7% 2.4% 12.4% 24.5%
4.4% 3.6% 13.9% 24.0%

Of course, you could combine the two formulae together and dispense
with the intermediate results:

=A4/A$2*A$1

Hope this helps.

Pete

On Jun 3, 1:53*pm, Helios150
wrote:
I am a teacher and have an excel spreadsheet (2003) with student's marks in
various columns. Each column represents a quiz/exam/OP or assignment and they
are all out of different totals. I need to know how to:
1) reduce each column to it's percentage value (example to follow) and,

2) to sum up totals from each row into a percentage final mark.

Assume we are using A1 as the first 5% piece of information, so the marks in
the columns would go from: A4:A34, B4:B34, etc

Thanks in advance,

Andrew

5% * * *5% * * *15% * * 30% * * * * * * 30% * * 15%
26 marks * * * *20 marks * * * *40 marks * * * *60 marks * * * *55 marks * * * * * * * *
Quiz 1 *Quiz 2 *Mid-Term * * * *Presentation * *Special Exam * *O/P
9 * * * 9.5 * * 33.0 * *49.0 * * * * * * * * * *
23 * * *14.5 * *37.0 * *48.0 * * * * * * * * * *
16 * * *9 * * * 33.0 * *47.0 * * * * * * * * * *
10 * * *9.5 * * 32.0 * *51.0 * * * * * * * * * *
7 * * * 9 * * * 18.0 * *10.0 * * * * * * * * * *
14 * * *12.5 * *31.0 * *53.0 * * * * * * * * * *
16 * * *15 * * *37.0 * *53.0 * * * * * * * * * *
19 * * *8.5 * * 40.0 * *49.0 * * * * * * * * * *
17 * * *12 * * *40.0 * *54.0 * * * * * * * * * *
13 * * *13 * * *31.0 * * * * * *43.0 * * * * * *
15 * * *9 * * * 30.0 * *53.0 * * * * * * * * * *
22 * * *9 * * * 25.0 * *55.0 * * * * * * * * * *
16 * * *20 * * *38.0 * *59.0 * * * * * * * * * *
18 * * *12.5 * *27.0 * *53.0
15 * * *11.5 * *40.0 * *49.0
16 * * *14.5 * *33.0 * *53.0
14 * * *10.5 * *37.0 * *53.0
11 * * *4 * * * 24.0 * *48.0
11 * * *11 * * *33.0 * *51.0
17 * * *12 * * *34.0 * *51.0
21 * * *9.5 * * 30.0 * *59.0
26 * * *17.5 * *34.0 * *48.0
16 * * *10.5 * *35.0 * *51.0
10 * * *10.5 * *18.0 * *54.0
14 * * *16.5 * *39.0 * *54.0
12 * * *11 * * *33.0 * *41.0
10 * * *7 * * * 22.0 * *31.0
22 * * *20 * * *40.0 * *59.0
7 * * * 4.5 * * 21.0 * *49.0
20 * * *17 * * *32.0 * *54.0 * *
23 * * *13.5 * *37.0 * *53.0 * *


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:36 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.