School Project Help Please!!!

Thegr8bigmouth1

New Member
Joined
Nov 11, 2002
Messages
4
I posted earlier today but I don't think I was very clear on what I need help with. I need to make a workbook with 5 pages 1 for each of my classes that show the way my grade for the class is broken down, all my grades I have recieved in the class, and what the grade in that class will be. For instance Psychology. In Psychology 25% of my grade is in-class/out of class assignments. 25% of my grade is made up of two papers each are worth 12.5%. And 50% of my grade is made up of two tests each being worth 25%. So in one column, lets say A, I have to put the pecentage and in the column next to it, in this case it would be B, I need to put the grade I recieved. The problem I am having is I haven't taken all my tests or wrote all my papers so I have empty columns. Lets take tests for example. On my first test I earned an 86. So 86 is 25% of my grade but I haven't taken the second test so when I factor that in most functions average a 0 into my grade. My teacher said that there is a function that I can use to get around this problem. He said that it is related to the IF function and sums based on conditions. I know I haven't described what I am doing very well but I hope someone understands and can help because I am really in a jam. Thanks
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Hi - welcome to the board!

Given that this is a school project, no spoonfeeding! So, first things first - check out the sumif and countif functions. An average of the sort you describe could be achieved with something like:

sum(all_your_test_numbers) / count(number_of_tests_taken)

...I leave it to you at this stage to work out how this might be achieved. You could also take a look at:

http://216.92.17.166/board/viewtopic.php?topic=22752&forum=2

...follow the link to Aladin's post for extensive info on conditional counting / summing.

Post back with more if you need, but don't just say "I couldn't get it to work" - indicate what methods you've tried, what results you got, why you think they're wrong etc. People on the board are understandably wary of 'helping' with school projects, but no-one minds asisting someone who's putting the effort in :)

paddy
 

Thegr8bigmouth1

New Member
Joined
Nov 11, 2002
Messages
4
Thank you very much for your help Paddy. I definately can see where memebers of this site must get very tired of helping with projects. Also, you're right, I should have posted more about what I have done so far. I realized this about a half hour after I posted. I have two of my five sheets working perfectly. What I used is the average function. I averaged all the grades I have this far and then multiplied the average by the percent they are worth. To calculate my final grade I added the totals of each catagory. This worked well for these two classes for two reasons. 1) all of the catagories are broken down into equal percents. So if there are 3 quizes and the quizes are 30% of my grade each one is worth 10%. 2) I have at least one grade in each catagory. Now I have ran into trouble. I have one class that the grade breakdown is: exams 30% 2=15% each, Papers 30% 6=5% each, Attendance 20%, and Final Exam 20%. Now since I have not taken my final exam yet this leaves a huge hole in my grade. Since there is nothing to average there is nothing to add to the other percents I have in my other classes. I should a 95% in the class but since 20% is missing I have a 75. I could just add 20 points in the formula, but the spreadsheet needs to be smple enough so that once I recieve my Final Exam grade I can just add that grade into the empty space and get my Final Grade for that course which then effect the summary sheet that has my GPA. My next problem came along when one of my classes had the Test breakdown as: Midterm 15% Final 20%. I could do each separately but then I stumble across the same problem as above. If I do the two together it will come out wrong because I will have 25% of zero which lowers my grade and I can not use the average function because there is a difference in percentage. I am stumped. I tried to figure out an IF statement and a SUMIF statement but no matter what I can't just add a grade and have the whole chart change. I am in desperate need of help because I have been sitting here for 6 hours trying different things out. Thank you again for your help and I hope I gave you enough information this time.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
example:

Put the following as headings in row 1:

a1: exam
b1: result
c1: running total

in a2: a7, put the following:
a,b,c,d,e,f
(i.e. 'dummy' exam titles)

in B2:B4 put the following:
50,85,70
(i.e. results)

in C2 put the following:

=SUM(B2:B7)/COUNT(B2:B7)

paddy
 

Thegr8bigmouth1

New Member
Joined
Nov 11, 2002
Messages
4
I probably should have told you beforehand how I have it set up. I bet that would have helped haha. I have it set up like for example A1 is the subject's name so Psychology then A4 is the title of one of the catagories the grade is broken down into so in this case it would be exams. Next in A6:A7 I have the percentages in this case 15% 15%. Then I have in B6:B7 the grades I have recieve in this case B6 would be 86 and B7 would be left blank because I have not taken that exam yet. Next I have in A8 Total and in B8 I have =average(B6:B7)*30%. Then I go onto my next catagory of grading and so on and so forth until I have finished all the catagories. Then I go and in lets just say A28 and put Final Grade and in B28 I put the sum of all the totals like the one in cell B8 and thats how I am getting my final grade. The problem is if I have a catagory like Final Exam which is the only part of the catagory when I add the rest of the totals I have I am missing that % which lowers my entire grade. What I need is someway to be able to raise the final grade by whatever % I am missing. I hope this clears things up alittle.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
imagine you had a set of results for 3 psyc exams. In the first row (B2:D2), you had the % of the total grade each exam represented (e.g. 25,25,50). In the next row (B3:D3) you had the % you got for each test (e.g. 50%, 80%, nothing in the next cell 'cos you hadn't taken the test yet). In the final row (B4:D4) you had the points for each test (12.5, 20, 0)

The following:

=SUM(B4:D4)/SUMIF(B3:D3,"<>",B2:D2)

I'm sure you can adapt this to your situation!

paddy
 

Watch MrExcel Video

Forum statistics

Threads
1,123,269
Messages
5,600,633
Members
414,396
Latest member
rinianjell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top