Spreadsheet Assignment 1 Reflection

October 24, 2016 Assignments, Google, Google Apps, Homework, IT Amber Blog, Reflections, Spreadsheets

Click here to view Spreadsheet Assignment 1

For our first spreadsheet assignment I created some data about credit cards. I then manipulated it to make it easier to read because with 500 rows of data it can be hard to read it and understand what has been collected. That is why I used conditional formatting and different formulas to make the data easier to read.

In my Raw Data sheet, each piece of information was put into its own individual cell. I did not want names to be separated so I used the CONCATENATE formula to join columns A and B together. I also joined the city and state in which those people lived, but I used the formula JOIN to do so.

Along with putting cells together, I also broke data up into different cells. I did this with the dates that each person received their credit card. I broke it up by month, day, and year. I used the SPLIT formula in order to do that successfully. I did this to help make things easier when I went to create my summary page.

I also wanted to count how many different credit card types there are in my data set. By using the formula COUNTUNIQUE I was able to count the number of credit card types. There are 16 (as reflected on my summary page), but three of those card types are widely known–Visa, MasterCard, and American Express. So I created columns to tell me whether each person has a Visa, MasterCard, or American Express card. I did this by using the formula IF. If a person has one of those cards, it would say “yes” for the corresponding column and also turn the cell green. If a person does not have one of those cards, it would say “no” and keep the cell’s default color (in this case white). I used CONDITIONAL FORMATTING to do this to make it easier to identify who has what card.

If the card holder did not have any of the widely known card types, then it listed the type of card they did have in the next column. I was able to use the formula IF again to do this, but there was a longer array, since there were more conditions. If the card holder did have a widely known card type, it would spit out a hyphen.

For my summary sheet I just wanted to summarize some key elements in my data set.

I used the formula SUM to add up the total number of transactions for all card holders. As I mentioned before, I used COUNTUNIQUE to count all of the different types of credit cards. I then was able to display each credit card name by using the formula UNIQUE.

To calculate percentages on the summary page, I used a complex string of formulas. It consisted of DIVIDE, COUNTIF, and COUNTA.

To find the number of people who had an American Express credit card, I used the formula COUNTIFS. This formula allowed me to count the number of people who had an American Express card and lived in California, as well as people who live Washington DC and Arkansas.

For the last few rows of the summary I used the COUNTIF formula to count how many people got cards in the months of April and October, which is why I split the dates into different cells. It also allows me and others to see the number of people who received credit cards during each year that this data was collected.