Who can help me with an Excel spread sheet?

djnoyes

New Member
Joined
Feb 12, 2005
Messages
13
Hello - I am having a great deal of trouble in creating an Excel spreadsheet I need as a teacher to track my students attendance, homework and tests.

This is the basic information. Total points possible for my 1st Term classes is 100 as follows:

Attendance 60 points - there are 12 classes in the semester.
Homework 20 points - there are 12 homework assignments in the semester
Test 20 points - there will be just one test given during the semester.

So, for each class (12) attended the student will recieve 60/12 or 5 points.
For each homework assignment (12) handed in the student will recieve 20/12 or 1.7 points
There will be only one test given during the semester and it will have 100
questions. So for each correct answer on the test the student will receive
20/100 or .2 points.

I would like to have the following column headings:
Attendance Points
Homework Points
Test Points
Total Points
Grade

The grade should be calculated as follows:
S = 100 - 90 points
A = 89 - 80 points
b = 79 - 70 points
c = 69 - 60 points
d = 59 - 0

I would greatly appreciate any help you could give me in designing this kind of spreadsheet. I have tried several times but have failed to create anything other than a big mess of unintelligible jibber jabber.

Edit: I forgot to mention that when the students attend class they are marked with an "0" and when they are absent they are marked with an "X"
When they hand in homework they are marked with an "F" for full credit which means it was handed in one time. The late homework is marked with an"H" which stands for half credit. Full credit would be 1.7 points and half credit would be 0.85 points. I also forgot to say that I need to have the column next to the student name a column entitled "Student ID". Also, I forgot to tell you somethng important. On the attendance part of the spreadsheet I need to be aboe to mark an "L" for late in addition ot the X and the 0. I need to be able to track how many X's or 0's or L's the student has for the 12 classes in the semester. Additionally, I need to be able to show the students by dates (for 12 classes in the semester) when they were X or 0 or L...so I need to be able to input 12 dates into the spreadsheet. Is that just far to complicated??? I sorry to be asking you for more of you time so if you don't have the time that is OK. I am a 65 year old teacher trying to learn Excel to use in my classes and I am having great difficulty understanding how to create formulas for Excel.

Thanks for you time......djnoyes
dj
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Pekkavee

Well-known Member
Joined
May 25, 2004
Messages
1,218
Something like this.
You can make 12 columns for Attendance etc.
Students.xls
ABCDEFGHIJKL
2
3StudentAttHomeTestTotalGradeAtt1Att2Home1Home2Testcorrect
4Peter101.71728.7dXXX85
5Lisa53.41523.4dXXX75
6John103.42033.4dXXXX100
7
8Grade
90d
1060c
1170b
1280A
1390S
Taul1


Formulas in English:

SUMMA = SUM
LASKE.JOS = COUNTIF
PHAKU = VLOOKUP

" ; " SHOULD BE " , "

I hope this helps

regards
Pekka
:eek: :eek: :eek:

PS. I don't know where the "nbsp" signs come from?? :confused: :confused:
 

djnoyes

New Member
Joined
Feb 12, 2005
Messages
13

ADVERTISEMENT

Hi....Yes that is wonderful!!!!

Thanks so much for you help. I wondered if you would be willing to help me a little bit more. For each student I need to have the next column entitled "Student ID"....this will be their student number. Also, I forgot to tell you something important. On the attendance part of the spreadsheet I need to be able to mark an "L" for late in addition ot the x and 0. I need to be able to track how many X's, how many 0's and how many L's there are for the 12 periods in the term. Also, I need to be able to show the students by dates (for 12 classes) when they were X or 0 or L. How and where can I input the 12 dates for the 12 classes in the term? I'm sorry to ask you for more of your time but I am a 65 year old teacher who is trying to learn Excel and I am having a dickins of a time with it. If you don't have any more time that is OK.

Thanks again -

djnoyes
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
Hi,

I'm on the point of knocking off work and going home, so will look at your further queries tomorrow unless someone else has already answered. meanwhile, a couple of queries. What is the "score" for an "L" or doesn't it change the attendance score. Your mark for present you show as "0" is that intended to be a zero or a capital O, as in o,p,q. It would be better if you used the aphabetic O to keep in style with your X and L.

More tomorrow.
 

djnoyes

New Member
Joined
Feb 12, 2005
Messages
13

ADVERTISEMENT

The "L" for the purposes of my spreadsheet, doesn't change the attendance score. That is because at the time I call and mark the roll at the beginning of class...if a student is not in class I mark an "X". However, if the student subsequently arrives within the first 30 minutes of a 90 minute class I change the "X" to an "L". I have to submit attendance reports to the University periodically, and they need to know the of "X's" and the number of "L's".

Yes, the "O" is suppose to be a capital "O", as in o,p,q.

Thanks for your willingness to help!

djnoyes
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
Hi,

My revised sheet is now too big to post in one go so I'll post it in two halves. If you PM me your email address I can email the worksheet for you to look at and adjust to suit the number of students you have, as my sheet is only set up for 4 students.

As you say you are a new learner, take a look at the formula, which are all fairly simple ones, then look at the descriptions in Excel Help.

Here's the left side of my sheet:
School register.xls
ABCDEFGH
1StudentStudent IDAttendanceHomeworkTestTotalGrade
2Eeny12335.0018.7016.0069.70B
3Meeny23445.0017.8515.8078.65B
4Miiny34550.0019.5513.6083.15A
5Mo56730.0013.6014.4058.00D
Sheet1
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
Now here's the right side:
School register.xls
IJKLMNOPQ
1Attendance RegisterHomework
2DateEenyMeenyMiinyMoEenyMeenyMiinyMo
331-Jan-05OOOOFFFH
47-Feb-05OXOXFFHF
514-Feb-05OOXXFFFH
621-Feb-05OOOXHFFH
728-Feb-05XXLOFHFH
87-Mar-05LOOXFFFH
914-Mar-05XOOOFFFH
1021-Mar-05XOOOHHFH
1128-Mar-05LOXXFFFF
124-Apr-05XLOOFHFH
1311-Apr-05OXOXFFFF
1418-Apr-05XOOLFFFF
15Attended79106
16Late2111
17Absent5326
Sheet1


Note that on the score table, left side, I just assumed you'd enter the test scores directly from the marked papers, I made no other provision for putting the number of correct answers down and multiplying by 0.2

HTH
 

Watch MrExcel Video

Forum statistics

Threads
1,123,279
Messages
5,600,696
Members
414,400
Latest member
Damocles2021

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