Excel and Statistics for Beginners

Summary - What should we know?

1. The meaning and method of Average, Mode, Median
2. The meaning and method of counting the Weighted Average
3. The meaning and formula for Correlation
4. The ways of recording and analysing the raw data
5. Choosing a suitable graphical representation of the survey results

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 PcSkillsZakladyStatistiky.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 articles linked below. 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.

___________________________________________________

Help:
Vážený průměr
Weighted Average in Excel
Excel Help - Weighted Average
Excel Help - 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