How I Used Simple Spreadsheet Functions and a cup of tea, to analyze a Data set.
So, I recently attended the Gitgirl School of data and I had to complete certain projects to be able to move forward and practice already learnt concepts, as any school would. Now, I'm going to keep this post interesting by including some other personal details as to how I got here. Its important I capture my reader’s attention as well as keep it, so they don't ‘skip’ to the next post after enduring the very first paragraph of mine. Okay, so lets move on.
I always believe that people who write code are the world changers, I mean, how cool is it, that one is able to tell a machine what to do, and it obeys, unlike some human beings who seem difficult to control (Not that I'm a colonial master or anything, LOL).
So, I learnt how to use functions like SUM, COUNT, AVERAGE, SUMIF,MATCH, ROUND, AGGREGATE, INDEX, SORT and a couple more. I'm just going to talk about the ones I used for this particular project.
The Data Set
So to understand this post, you’ll have to know what the given dataset was all about. Its a record of Sales made by a store from the year 2014 to 2016 and as expected, the data was a little disorganized. it had several rows and about 10000 columns. Yep, I know what you’re thinking, that’s a whole lot of figures, but it was easy to sort through the entire data set in a short time.
I usually like to do a little bit of massaging before I start to study any dataset. what I mean by that is, I try to differentiate my headers from the rest of the data by making them bold and/or italicizing them. I also freeze the first row and column, (i.e. the ones containing the header and serial numbers) so that no matter how far I am in the worksheet, I am never lost. I also expand the cells so that no overlapping text is hidden. so you see, a little touching here and there and After doing all that, the worksheet looks like this;
Now, with my worksheet looking like this, i can easily understand the data set and start my analysis.
Now, for this particular project, I didn't have to brainstorm questions, I was already given a set of questions to answer. For Instance, one of my favorites was the CONCATENATE function. As the name implies, it joins two different strings together (if you're familiar with a bit of programming). I had to Join the State column and the Region column. Seeing that these functions are already inbuilt, once you type it in your worksheet, the arguments to fill in pop up and with that I was able to create another column which would now contain the joined columns. The best part was, I only typed the formula once, all I was left to do was ‘click and drag’ automatically, the same formula was applied to the remaining 9995 cells.
Now, i actually started this project of mine after the deadline for submission had passed. we were given approximately 3weeks to study the course content and work on the project slash write a blog post, as i am doing now. I didnt know how time had passed until the final submission day came and hit me right in the face. I had to plead for an extension and promised to be done in three days, sounds like a suicide mission right?, I know, but never underestimate the power of an analyst, at times you would have to meet up crazy deadlines and work under a lot of pressure. I started to study the curriculum while simultaneously working on the project, anytime I got stuck, I would refer back to the lessons, and when I felt my head was getting messed up, I would check WhatsApp, reply few messages, laugh at status updates and continue working. This kept me from going insane. Honestly.
I was also asked to calculate some sales and so the SUM function was there for me. all I had to do was type in the SUM function in a cell, and input the range I wanted added up, which in this case would be the entire sales column, and boom! I had my grand total in seconds.
The Hard Part
For me, when I got to the part where I had to create a VLOOKUP, I froze because each time I tried to make a move, I got an error. At some point, I thought my computer was fighting me, not until I settled down and a few hours later, I got the hang of it. Basically, VLOOKUP means Vertical lookup, and like the name implies, it looks up stuff, vertically. After going through some hurdles, I can now say, I'm a VLOOKUP guru, and that's an example of what the learning process can be like.
The Cherry On the Pie
So here I am, writing my very first official blogpost about something I learnt, so others can read and learn too, smiling, cos I made it through my project. I was able to answer all the questions asked, used my functions properly and now I'm feeling pretty good about myself. In this study, I didn't have to create any visual presentation, hence no graphs or charts to display. I have currently set a reminder on my TV for “How I met your Mother” one of my Favorite shows, because, I know I'll be done with this, before the show starts and I can cross my legs and enjoy, why, “Another Project, Done and Dusted” LOL.
If you have made it up till this point of my story, I must say, You are also a world changer. Doing it one blogpost at a time. I'm sure you're wondering where the cup of tea came in, well, Its not a function, just a little motivation… Thank you so much for reading.