Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 * * |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |