J. McKenzie Hansen
Spreadsheet Assignment 2 Reflection
This spreadsheet assignment was WAYYYY harder for me than the first one. I decided to use the data collected from my EAST project’s web portal to show different information about the HRCCL EAST program and my students’ progress. I had a lot of data to manipulate and summarize, and it was very hard and intimidating for me to start working on it.
The QUERY function made it easier for me to work and manipulate the data. I really enjoyed using it to create nice labels to go with the data that I needed to represent. I did run into a couple issues (that I was able to overcome later) while using QUERY.
The first issue was, I was trying to show the number of approved projects with the number of unapproved projects. When I would type the string
=QUERY(projects, “select count(A) where F=1 label ‘Approved'”) or =QUERY(projects, “select count(A) where F=0 label ‘Unapproved'”)
it would give me the correct count, but not in the correct format— it spits out the count below the label. When I went to put that in my chart, I either ended up making the chart show 100% for the number 4 or got the correct percentages but they had no labels.
After I realized that that didn’t / wasn’t going to work, I tried making it list every project with a “yes” or “no” output. That didn’t work because in order to make a chart, you HAVE to have a value / number. So I realized I had to do COUNT.
It was really difficult to figure out, but after some research and reading over my class notes, I figured out I needed to use Group By to help me, as well as creating more columns in my data sheets to help me accomplish what I needed. So my string formula is now
=QUERY(projects,“select T, count(A) where F=1 or F=0 group by T label count(A) ‘Number of Projects’, T ‘Status'”).
I also took full advantage of using OR to help me complete this. I am very proud of myself for (finally) figuring it out and then being able to use the same concept for other areas on my Summary sheet.
Another problem I had while using QUERY, was the basic structure. It is VERY picky about the order it goes in, as well as all of the commas, single quotes, double quotes, etc. It was very hard to make sure that I had all of those things correct before pushing enter. And then if / when it didn’t work, the structure of the formula is the FIRST thing I went to look for–and most of the time, it was the problem. I also set myself up for confusion because I had to change my ranges a lot in the spreadsheet due to me adding more columns to help me organize and manipulate my data.
This assignment DEFINITELY taught me patience! And there were times when I would get really irritated or upset, but I would just step back for a second to take a break and then start at it again. I believe that I did a great job, and the best job that I could, on this assignment, it just took a lot of time and energy to push through and troubleshoot errors.
If I had to do this assignment again, I would make sure that I had all of my data organized the way that it needed to be from the start, that way I wouldn’t confuse myself with why my ranges aren’t working. I also would make sure that I know the correct syntax for the QUERY function so I could eliminate as many of those errors as possible– which would save time and frustration.
This assignment was a challenge for me, but I was able to overcome it without losing for my love for data and spreadsheets! In fact, though it was irritating, I believe this assignment helped me grow my love for spreadsheets even more. I am very impressed with my “final product” and I cannot wait to show it to my EAST Directors.