Prev: Count "," in a cell
Next: Function to concatenate cells in a range
From: Helios150 on 3 Jun 2010 08:53
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

From: Pete_UK on 3 Jun 2010 12:09
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 <Helios...(a)discussions.microsoft.com>
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    

 | 
Pages: 1
Prev: Count "," in a cell
Next: Function to concatenate cells in a range