averaging and if statements

[jpeg]

ok so i often overthink formulas, but i'm hoping someone can help here.

I just need to understand the basic formula here so i can do this without a million IF statements (i hope).

This is for a teacher (my girlfriend) and I'm hoping to make it as simple as possible so she doesn't have to change any formulas. She's definitely NOT an excel teacher heh.

Anyways, here's the setup.

I have 5 columns - quizzes, assignments, projects, exams, and overall. They will be weighted in b1 2 3 4, and b5 being an overall average of whatws completed. In the most basic of terms, I would like this overall column to give me a running average of whats been completed so far.

So if there are quizzes but no assignments, it weights only against the quizzes total.

For example. Mary gets 70 percent on her first quiz and 60 on her second. She therefore has an average of 65%. If quizzes are worth 20% of her final mark, but no other assignments have been completed yet she will show 65% in the overall column.

If there are assignments as well, representing another 20% of her final mark.. but she hasn't completed any... she will have 32.5% overall. If she HAS completed assignments, it will do the same with that portion and total only the quizzes and assignments portion. So 40% of the overall courseload is complete, but it will say overall what that student's average is based on what has been completed so far.

This may sound confusing, but hopefully it makes sense if you read this while viewing the attached excel sheet. It works right now provided that ALL of these have at least 1 entry. The "Final Exam" only gets calculated in if and when if is added to the sheet.

row 2 requires an "a" "q" "u" or "p" for the quizzes and assignments, unit exams and projects respectively. an "f" will indicate the final exam.

row 3 is what the a q u p or f is marked out of. the white and gray cells are where the mark would be written in and the yellow auto-calculates the percentage on that exam/quiz/etc.

take a look and let me know if you have suggestions or need more explanation. I would appreciate any help you can give with this. I've been working on it too long and it's now getting frustrating haha.

[MarvinP]

Hi jpeg and welcome to the forum.

I used to teach math and bias against averaging or weighting percentages. Maybe you should read these before working on your girlfriends affections.
http://wiki.answers.com/Q/Can_you_average_percentages
http://www.ericdigests.org/2003-4/sc...ilization.html
http://statistics.laerd.com/statisti...dard-score.php
http://www.teachersandfamilies.com/o...nt/scores2.cfm

Sorry for not answering your question but I think more teachers should know it is just wrong to average percentages. I do believe more people should look at average student scores and standard deviations to help with grading.

[jpeg]

Hi jpeg and welcome to the forum.

I used to teach math and bias against averaging or weighting percentages. Maybe you should read these before working on your girlfriends affections.
http://wiki.answers.com/Q/Can_you_average_percentages
http://www.ericdigests.org/2003-4/sc...ilization.html
http://statistics.laerd.com/statisti...dard-score.php
http://www.teachersandfamilies.com/o...nt/scores2.cfm

Sorry for not answering your question but I think more teachers should know it is just wrong to average percentages. I do believe more people should look at average student scores and standard deviations to help with grading.

thanks for the reply Marvin. She's actually a math teacher as well haha.

I originally planned to do the "total marks given / total marks maximum" option, but she would prefer to know where a student sits by averaging out the percentages. While I didn't understand this at first, after some thought it kind of makes sense that if you have a student fluctuating wildly you will get a more accurate picture of what he deserves.

For example, if a student gets 100/100 on a test, then 1/20 on a test, then 1/10... he would still have 102/130... or about 78%. By averaging the percentages, you actually get 38%. This is just a rough example, but kind of where she was heading with this. She does let her students know at the beginning of the year that small assignments hold as much merit as big ones this way, so it's fair... and the more i think about it, the better i feel about it.

If I were a student who got 78/100, 17/20 and 7/10 I would end up with an average of ~78%. If another student had the example above - i'd be pissed that we're in the same category just because he had one good test. Conversly, I would be pissed if 1 bad larger test threw my percentage away completely. (say 30/100, 10/10 and 20/20...)

But alas, it's not my decision either way... i'm just helping design something that'll help her get through the year a little easier haha =)

cheers
JPeG

[inayat]

Hi jpeg,

I have one question. Will all the students be taking all the tests, assignments, etc. Example is it possible that Student A takes four assignments while Student B takes only 3 assignments. And if it is possible will Student B judged only on the basis of 3 assignments.

Edit: Another thing i noticed is that the sum of the percentages for value is more than 100%.

[jpeg]

Hi jpeg,

I have one question. Will all the students be taking all the tests, assignments, etc. Example is it possible that Student A takes four assignments while Student B takes only 3 assignments. And if it is possible will Student B judged only on the basis of 3 assignments.

Edit: Another thing i noticed is that the sum of the percentages for value is more than 100%.

I believe her goal is to have a mark for all students taking all tests and assignments.

The way the percentages work is that the first four (all but the final) will add up to 100% of the in-class mark, but in the end be worth only 70% of their final grade. So, if you get 50% on your in class, you have 35% plus whatever you get on your final (let's say 60%) divided by 30...

so a student who got 70 in the in class work and 66 percent on the final would end up with a final of 35 + 20 for a final grade of 55%.

So those percentages are up there for her to change her grading as necessary. i.e. if she decided to change the weighting from 20 20 20 40 to 20 30 30 20 it would be simple and she wouldn't have to touch formulas.

So all students will be judged on all assignments / quizzes / etc the class has had. i believe she will be giving each student the option of writing / resubmitting missed work at the end of the year for part marks (or perhaps throughout the year.. i dunno.. i just work on things to make her life easier haha) so all should be accounted for.

as it stands, each time she adds something she just puts the title, then q a u f or p , then the max score, then the students individual score... it calcs percentage for that particular test/quiz/exam/etc and totals up that stuff to the left. the overall then needs to reflect overall what's been done in relation to whats been completed.

so 5 tests with a 50 average would give a 50 overall, but if you add assignment it weights that to actually be only worth 25 (since quizzes and assignments are both equal in weighting and are the only two being used)... so that student would get 25% in their overall score plus whatevr their assignment worked out to (let's say 80%) for a total of 65% overall.

I hope that makes sense. this is way harder to type and explain than i anticipated haha.

[royUK]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

PLEASE PM WHEN YOU HAVE DONE THIS AND I WILL DELETE THIS POST

[jpeg]

well... good news... i figured it out on my own =) i managed to use an if statement to sort it all out and it seems to be working fine now (huzzah!!) thanks for looking though.