Lesson 3 – Objectives

1. Lesson 3– Objectives Understand how layout will effect analysis Enter data into a spreadsheet Use formatting and validation to make the job easier.

2. What you will need to do • Your job is now to get the data into Excel • Each persons response must be entered into a table in order to allow analysis to occur. • You must follow these rules • Each persons responses will be on a SINGLE row • There will be no blank rows / columns between data • Titles will be clear and responses separated with borders / colours.

3. Example layout Above is the basic idea. This is a starting point.

4. Example layout Formatting will enable answers to stand out.

5. Using validation When entering large amounts of data it is always advisable to use validation. Validation will prevent erroneous data from being inputted in the first place! It is not fool proof but it does prevent typos!

6. Do this on a separate sheet! You can copy and paste your questions and answers into the second sheet. It is important that you may it out like above!! Otherwise you will create MORE work for yourself.

7. Do this on a separate sheet! Now highlight the responses and then enter “question1” (no spaces) in the small white box. This will name the selection. This makes the next part MUCH easier!

8. Setting validation Go back to your responses. Select the first column for all respondents.

9. Setting validation Now select data validation.

10. Set it up as follows

11. Data validation Doing this will create a drop down for every responses. This REDUCES the chance of errors which means that your analysis will be more accurate. This must be repeated for every question.

12. Automatic tally The first step in analysis is to calculate totals. To do this quickly we are going to use COUNTIF The next screen shows how this could be done

13. Example

14. Important points You must select the correct range for each question. It will NOT be identical to the example. The criteria, or what we are looking for, can be a cell reference! In this case it is pointing to the responses for question 1. Absolute cell referencing (the $) allows us to use fill down. This reduces the amount of work we have to do.

15. What do we need to find out? • The next step is to come up with a number of questions about our results. • For example • How many students use social networking? • How many students use old computers? • How many students have had a virus? • How many students have to share a PC?

16. More advanced questions • You may wish to combine questions as these will produce the most interesting results AND will get the highest marks. • For example • How many people have had a virus and download illegally? • What percentage of males and females play games online?

17. Creating simple graphs • Now that you have totals creating graphs will be easy. • Ensure that you • Select a suitable graph type • Have titles • Make sure the data is easily read and makes sense. • Create graphs which will help you answer your questions.

18. Combined analysis • To combine questions we can use a special version of COUNTIF called COUNTIFS • This allows us to combine multiple COUNTIF’s together (which is why it is called COUNTIFS!) • You can combine as many together as you want • =COUNTIF(range, criteria, range2, criteria2....)

19. The original data

20. Where males use their computer Notice that the second criteria has “Male”. So row 4 is looking for Bedroom AND Male.

21. Compare the results

22. Graphs with multiple series

23. What have we found out? You should look at your graphs and then answer your questions. Try and think about what it tells you. Do not just look at the obvious. For example if you find that more males download music illegally then is there a reason for that?