Who can help me with an Excel spread sheet?

[djnoyes]

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

 

[Pekkavee]

Something like this.
You can make 12 columns for Attendance etc.

Students.xls
	A	B	C	D	E	F	G	H	I	J	K	L
2
3	Student	Att	Home	Test	Total	Grade		Att1	Att2	Home1	Home2	Testcorrect
4	Peter	10	1.7	17	28.7	d		X	X	X		85
5	Lisa	5	3.4	15	23.4	d		X		X	X	75
6	John	10	3.4	20	33.4	d		X	X	X	X	100
7
8		Grade
9		0	d
10		60	c
11		70	b
12		80	A
13		90	S
Taul1

Formulas in English:

SUMMA = SUM
LASKE.JOS = COUNTIF
PHAKU = VLOOKUP

" ; " SHOULD BE " , "

I hope this helps

regards
Pekka


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

 

[ellin00ra]

Didn't get into it , but any help here:
http://office.microsoft.com/en-us/templates/CT063455541033.aspx




-ellinoora-


(Moikka Pekkavee )

 

[Fergus]

Something like this maybe?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Student	Attendance	Homework	Test	Total	Grade	Attendance Register				Homework
2	Eeny	45.00	18.70	20.00	83.70	A	Eeny	Meeny	Miiny	Mo	Eeny	Meeny	Miiny	Mo
3	Meeny	55.00	17.85	19.00	91.85	S	X	0	0	0	F	F	F	F
4	Miiny	50.00	19.55	15.00	84.55	A	0	0	0	X	F	F	H	F
5	Mo	35.00	15.30	18.00	68.30	C	0	0	X	X	F	F	F	H
6							X	0	0	X	H	F	F	H
7							0	X	0	0	F	H	F	H
8							0	0	0	X	F	F	F	H
9							0	0	0	0	F	F	F	H
10							0	0	0	0	H	H	F	H
11							0	0	X	X	F	F	F	F
12							X	0	0	0	F	H	F	F
13							0	0	0	0	F	F	F	F
14							0	0	0	0	F	F	F	F
Sheet1

 

[djnoyes]

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]

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]

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]

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
	A	B	C	D	E	F	G	H
1	Student	Student ID	Attendance	Homework	Test	Total	Grade
2	Eeny	123	35.00	18.70	16.00	69.70	B
3	Meeny	234	45.00	17.85	15.80	78.65	B
4	Miiny	345	50.00	19.55	13.60	83.15	A
5	Mo	567	30.00	13.60	14.40	58.00	D
Sheet1

 

[Fergus]

Now here's the right side:

School register.xls
	I	J	K	L	M	N	O	P	Q
1	Attendance Register					Homework
2	Date	Eeny	Meeny	Miiny	Mo	Eeny	Meeny	Miiny	Mo
3	31-Jan-05	O	O	O	O	F	F	F	H
4	7-Feb-05	O	X	O	X	F	F	H	F
5	14-Feb-05	O	O	X	X	F	F	F	H
6	21-Feb-05	O	O	O	X	H	F	F	H
7	28-Feb-05	X	X	L	O	F	H	F	H
8	7-Mar-05	L	O	O	X	F	F	F	H
9	14-Mar-05	X	O	O	O	F	F	F	H
10	21-Mar-05	X	O	O	O	H	H	F	H
11	28-Mar-05	L	O	X	X	F	F	F	F
12	4-Apr-05	X	L	O	O	F	H	F	H
13	11-Apr-05	O	X	O	X	F	F	F	F
14	18-Apr-05	X	O	O	L	F	F	F	F
15	Attended	7	9	10	6
16	Late	2	1	1	1
17	Absent	5	3	2	6
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