First November Post

Today, I learned how to clean data and analyze it using Excel. Excel has been an essential tool to collect, clean, and graph data. Although I have some experience in Excel, I had very little experience on cleaning data effectively and analyzing it. To learn it, I was given a real dataset surveyed by Craig, my mentor. The survey was took by 154 college students and faculty regarding laundromat. Questions include on which days they most often do laundry, how much laundry costs, and typically how many loads per at once.

From the data collected, I first created a new sheet where I made a table for each question so that it is clearer to look at a specific number for each answer.

As the picture above shows, column A has the questions and possible answers. For column B, I made a equation that counts the number of students who answered only to the specific question. For example, for students who answered they do laundry on Monday, the equation, =COUNTIF('Form Responses 1'!H2:H155,"*Monday*"), will picks up only the students from the data who do laundry on Monday. I did this to the rest of the result. Now that I have a clean data with specific number of students, I made a new column, column C, where I set an equation to calculate proportions to each answer. To find the proportion of students doing laundry on Monday, I put an equation, =B2/$B$9, which divides the number of students on Monday by the total number of students.

My task was to find one most effective day in week to open the laundromat if I could open it only once a week. I chose Sunday because 31% of the people who took the survey do laundry on Sunday. This is equal to approximately 47 people (31%*154). Using the proportion for the laundry loads found previously, 14 people have 1 load (47*31%), 25 people have 2 loads (47*53%), and so one. Based on this, I was able to assume the total number of machines would be used on Sunday and the money that would be raised, which was $267.73 in total.

Today's exercise was pretty simple but necessary to understand deeply. I think it was the best and most appropriate way to get started since it is similar to how the company actually does to read trends and predict sales. Next week, I will learn a new programming language which is similar to Excel but include advanced knowledge.