Excel and Statistical Functions for Teachers

Problem 1 : How should a teacher use excel for class evaluation and statistics?

Download two files (excel_start and excel_1) from http://prekladani.capsa.cz/, Folder PCSkills.
The "start" file shows erratic notes on Ss results, saved in WORD.

Task 1 - Editing
Follow the instructions in the word file - your task is to create a highly practical list of students ond their results in Excel format.

TIPS:

1. Consider which headings are missing, and add them.
2. Order the students alphabetically.
3. Find out how to transfer the sheet from word to excel without too much work :)

Task 2 - Statistics

1. Prepare some statistics – AVERAGE results of each test, of each student, the best and the worst mark of each test (MAX, MIN) and of each student.
2. Find out what MODE and MEDIAN mean. Use them.
3. Use the form COUNTIF for finding how many Ss got 1 during the whole term.
4. Assign different weight to the marks and apply the weighted average to get the final mark for each student. 
5. Express some of your statistical data in graphical forms. Which types of graphs should you use?

Help: Vážený průměr
Weighted Average in Excel
Excel Help - Weighted Average
Excel Help - Descriptive Statistics

Problem 2

Analysing research data

Task 1 - Finalising the Survey
1. Insert the raw data of your survey into a spreadsheet. Remember - on cell, one piece of information, ideally a number! give each repondent an unique number, and use one line in the spreadsheet for each respondent.

You can download an example spreadsheet from capsa - go to folder PCSkills, file ExampleResearchMatrix.xls.

2. Use the sum, average, mode and median formulas to get the first results. THINK about the type of data you are using and consider you want to learn - use the predefined questions and hypotheses.

3. In some surveys it is very important to mutually relate various findings -  to prove or disprove a correlation between two facts/answers. Identify the possibilities and explore the correlation within your data.

4. Prepare a graphical presentation showing the results of your survey. Decide which types of graphs are the most suitable.

5. Choose the most revealing and interesting results and comment on them. What factors influenced the results?

6. Prepare a presentation of your results in PREZI and share the link in the comments below.
Compulsory items:

• Topic, questions and hypotheses
• Survey methods
• Methods of dissemination
• Target group, the final number of respondents
• Summative results (in %)  (illustrated through graphs)
• Correlations
• Conclusions

7. Insert the link to your online survey questionnaire and the link to the final Prezi presentation into your webpage!

___________________________________________________

Task 2 - Supporting activities

1. Downolad the excel file KorelacePriklad.xls form capsa. Use the formula explained in this link: Korelace v excelu - příklad to find out the correlations among the given sets of data.

2. Downolad the excel file Verejny... .xls form capsa and explore the formula used for counting individual teaching loads: =DSUMA(I7:J116;"Úvazek";L1:L2). How does it work, what does it summarise?

3. Study the following articles. You should understand, and be able to explain and apply at least the descriptive statistics. Ask the tutor for help if necessary.


Tips:
Pie graphs are used only when describing the split of a limited sum of recipients or other data.
Example: Identifying the use of potatoes in the kitchen:  50% get boiled, 25% are mashed, the rest is used to make soups.

Focus on the examples - try to understand the information in the data before dealing with the methods.
Consider real-life examples,  or use the real data to experiment with descriptive statistics.

Descriptive statistics - Distribution, Central Tendency (Average, Mode, Median), Dispersion, Standard Deviation
Correlation example

Úvod do popisné statistky
Popisná statistika
Co je korelace?
Čtyřpolní a kontingenční tabulka
Měření závislosti, korelace a regrese