Learn Excel Part Deux
|
For the upcoming assessment you will need to play detective.
You will be given a large set of data You will then be asked to pick the data that is relevant, use algorithms to sort it and then display that data in a reasonable fashion. We will need a brand new set of data for this, Please download the files below. 
Download Activities
| ||
Filters and Crime Mysteries
Creating filters
1.) Open the suspects.xls 2.) Click "Data" --> "Filter" Now the filters have been created, try to show people only over the age of thirty 3.) Click the drop down next to age 4.) Click "Number Filters" 5.) Enter "30" You should get 30 results |
|
|
Mystery One - The missing Pajamas
Someone stole Mr Tyler's Pajamas and he is very upset. Luckily we had some witnesses. Lets see if we can hunt the person down from the statements. "I saw some old dude limping away with the pajamas... He must have been over 65" - Frank Lamb "Maybe it was me, I have a tendency to steal things while I sleep walk" - Boris Weiss "How dare you accuse me I was at the bakery with Rose, She is the brown haired girl." - Reggie Smith "Yeah someone, was wearing pajamas around like they were cool. I didn't get a good look but they had a face tattoo. Me and Stanley were having a cigarette at the time". -Mike Grant Someone is lying..... Answer: Reggie Smith, was one of two men over 65 and lied about Rose. |
Mystery Two - The Dead Baker
Lying on the floor was a dead baker from Bradshaw's Bakery. He must have died in the last 2 hours. It looks like a knife fight gone wrong. Who Killed him... Lets get some facts. Forensics have come back and said that there was two different types of blood on the floor "B" and "A" One of these is the victim and the other is the murderer. "Maybe it was me, I have a tendency to get into knife fights while I am sleep walking" - Boris Weiss "I heard a male and female voice screaming at each other from the bakery at the time of the killing" - Mary Morris "The dogs cannot get a good scent of the killer as this strong perfume is putting them off". - Police Sergent "Wasn't Bradshaw dating and working with some blonde?" - Some random. Answer: It was Patricia Bryant the Bakers Girlfriend ,looks like this was a case of knife fight gone wrong. |
Mystery Three - Arson at Alan''s
.Alan DaSilva's butchery was burned down while he was getting his hair cut.
Detectives found gasoline and a bottle nearby along with a receipt. Lets find out who did it. "Maybe it was me, I have a tendency to light fires while I am sleep walking" - Boris Weiss "I don't know it was busy ok... I know in the rush of things the guy who purchased the bottle had some weird accent. It was either french or American...perhaps German?" - Ruth "Some young guy with a mustache filled up some gas here (20's - 30's)." - Sammy "Hmmm... I around the time of the arson I was cutting the hair of some short fellow from America" - Barber Blanc Pierre Answer: Alan burned down his own bakery for the insurance money |
Mystery Four - Organised Crime Lord
We are close to finding the kingpin in an underground sting operation. Our under cover agent has determined that the king pin is a surprisingly young male. This was before our undercover agent was killed. But he did manage to give us some potential suspects and witness statements before he died. "Maybe it was me, I have a tendency to run highly sophisticated organized crime syndicates while I am sleep walking" - Boris Weiss "His height? around 1.6... something... I don't know" - Goon 2 "The boss definitely wears an eye patch... or was it an earring? glasses? I forget which" - Goon 1 "I saw him, I swear it! He was walking confidently and calmly, then he had a conversation with a guy." - Goon 3 "He talks like he some git from Europe" - Goon 4 Answer: It was Boris!!!, He must have ran a highly sophisticated crime syndicate in his sleep. |
CountIf, SumIf and AverageIf
|
Using filters is useful if you really want to find less then ten records, However if you want the real statistics you want to use CountIf, SumIf and AverageIf.
These allow you to modify your search results based on criteria. Open Up "Sales Info.xlsx" What we can see is a lot of sales information all over the world Task: Your boss wants information about profits from different countries and different business segments. For now lets get rid of information we don't need 1.) Highlight the 3 columns "profits", "Country" and "Segment" 2.) Hit Ctrl + C to copy those columns 3.) Create a new tab 4.) Paste the info into a new tab 5.) Make it pretty Task: Test average, count and sum functions 1.) Create some headings on the right for average count and sum. 2.) Run those different formula on the profits 3.) Check your answers with the picture on the right What does each formula do? Complete these queries for your boss Your boss wants the following information: A.) The average amount of profit from small businesses B.) How many times deals are done in France C.) The sum amount of profit from Germany. Try to figure out how SumIf, AverageIf and CountIf work to solve the queries 
|
|
Presenting Data
|
When presenting data for the upcoming assessment, you will be marked on presentation. It is simply not enough to come up with the answer, it has to be presented in a well structured clear manner.
Lets look at an example: The boss wants to know how much profit was made from each different segment! The first thing I would do is write out each segment like on the right. Then use sum if to find out how much profit comes from each sector (Extra for experts use the field name on the right in the formula). Then you would need to pretty it up and add labels. The boss wants this presented in graph format. As this isn't a change over time or a percentage, then it would be appropriate to create a bar graph Make sure all the Axis are labelled. 
|
Two Tasks
1.) Complete the task as stated previously about profits from each segment 2.) The boss also wants profits from each country, repeat the previous activity but for countries. |
Summary Sentences
|
A helpful skill is to be able to take a query and turn it into a summary sentence.
For this next section we are going to look at the Census.xlsx file. (This is information from a random group of 500 teenagers living in New Zealand). We are going to look at this query: What percentage of the teenagers from New Zealand know more then one language? 1.) Create a new tab 2.) Copy the relevant data over to the new tab (The languages and country column. 3.)Sort the date by Country, delete every row of data that doesn't have New Zealand has a country 4.) Use a count in the languages column to find the number of students in total 5.)Use a count if to determine how many students know more then one language. 6.) Work out the percentage by dividing the language students with the total students. 7.) Open Microsoft word and write a sentence like this Out of the 385 students that were from New Zealand, 28% knew more then one language |
Tasks:
Follow the same process for the following inquiry questions: 1.) What is the makeup of eye colour of the students, please graph your results. 2.) What is the most prominent eye colour of students over 160cm 3.) How many students user facebook? How many used reddit? Youtube? 4.) Graph the previous results Present findings on a nice word document. |
Practice
Treat this like an assessment
Dr Manning from the manning institute has some questions about New Zealand teenagers. He wants a one page report to answer the following questions
1.) How many of the teenagers live in Auckland, as opposed to other regions. (graph your results)
2.) What is the average travel time to school for students that walk? bike? bus? Motor? (graph your results)
3.) What is the sum total of bag weight for students that walk or bike to school? (assume blank is 0)
Dr Manning from the manning institute has some questions about New Zealand teenagers. He wants a one page report to answer the following questions
1.) How many of the teenagers live in Auckland, as opposed to other regions. (graph your results)
2.) What is the average travel time to school for students that walk? bike? bus? Motor? (graph your results)
3.) What is the sum total of bag weight for students that walk or bike to school? (assume blank is 0)
