sorting grades

doccsteve · Apr 22nd, 2002, 07:00 AM

I'm a teacher. My students took 5 tests this semester. I keep all roster data on Excel. Homework counts 20% and I want the tests to count for 80%. Instead of each test counting 16%, I want to find the highest test score and weight that most heavily; the second highest test weighted second most heavily; and so on to the lowest score which would be wighted least heavily. I have done this in the past with a max of 3 tests, and the embedded "if" statements were nearly out of control. Also, excel seemed to have some maximum number of characters that it can access within a formula, and I exceeded that. I haven't done anything with macros or VB, but in years past, I did some database programming.

lenze · Apr 22nd, 2002, 07:46 AM

Hi Doc:
One easy way would be to use the LARGE function. Say a student's 5 test scores are in C3:G3. Then in another columns enter =LARGE(C3:G3,1)*'weighted factor for best', Then, in the next column enter LARGE(C3;G3,2)*'weighted factor for second best' Repeat for the next three scores.

Big Blue · Apr 22nd, 2002, 09:35 AM

I'm on dangerous ground here, trying to teach a teacher

but here goes -

1. weighted averages are generally rely on volume or quantity to provide the weighting. As this doesn't fit your situation,you'll first have to decide how much each test score should be weighted.

I've enclosed a good weighted average write up by a Microsoft MVP.
hope this helps.

http://www.beyondtechnology.com/tips011.shtml

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 22nd, 2002, 07:00 AM	#1
doccsteve New Member Join Date: Apr 2002 Posts: 1	I'm a teacher. My students took 5 tests this semester. I keep all roster data on Excel. Homework counts 20% and I want the tests to count for 80%. Instead of each test counting 16%, I want to find the highest test score and weight that most heavily; the second highest test weighted second most heavily; and so on to the lowest score which would be wighted least heavily. I have done this in the past with a max of 3 tests, and the embedded "if" statements were nearly out of control. Also, excel seemed to have some maximum number of characters that it can access within a formula, and I exceeded that. I haven't done anything with macros or VB, but in years past, I did some database programming.

Apr 22nd, 2002, 07:46 AM	#2
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	Hi Doc: One easy way would be to use the LARGE function. Say a student's 5 test scores are in C3:G3. Then in another columns enter =LARGE(C3:G3,1)'weighted factor for best', Then, in the next column enter LARGE(C3;G3,2)'weighted factor for second best' Repeat for the next three scores.

Apr 22nd, 2002, 09:35 AM	#3
Big Blue Board Regular Join Date: Mar 2002 Location: Ontario Posts: 52	I'm on dangerous ground here, trying to teach a teacher but here goes - 1. weighted averages are generally rely on volume or quantity to provide the weighting. As this doesn't fit your situation,you'll first have to decide how much each test score should be weighted. I've enclosed a good weighted average write up by a Microsoft MVP. hope this helps. http://www.beyondtechnology.com/tips011.shtml