If you have been following the progression of Full Spectrum Learning, you may know that we are in the process of implementing and reaching out to professors for our new data grid!
This grid has been such a process and a learning experience for me. There have been a few major concepts I have learned including:
- How much I love spreadsheets and formulas
- How complicated a simple visualization actually is
- How easy it is to become discouraged
1. How Much I Love Spreadsheets and Formulas
Now, this may not seem like much of a surprise if you know me, but for some reason, I forgot how much I love to write formulas and how satisfying it can be. I mean formulas basically combine my two favorite things: code and math.
This data grid uses a multitude of formulas. First of all, it takes the form responses (all in words [or strings]) and converts them into numbers [or integers]. The easiest way to solve this problem was to do all of the math calculations on a separate sheet of the same spreadsheet as the responses.
The newest formula I learned how to use was an Array Formula. The main idea of this formula is to be able to apply a specific formula to an entire column instead of just a specified range. This came into importance when for some reason all of the formulas liked to change line numbers once a response was added on the main sheet of the spreadsheet (it was odd). However, it was very helpful to have the array formula set and then never have to worry about it again. Other formulas used include a VLookup, Countif and many If/Then statements.
Here is what the spreadsheet of formulas looks like currently:
In case anyone else is interested in this, the way it works is:
- The responses are translated into number format using a large if/then formula (columns G and H)
- The corresponding intersection number is found by multiplying the engagement number by 10 and adding the technology number. This is solely for the purpose of creating a unique number for each intersection in order to be able to count the responses in accordance with each intersection (column J)
- Column J is then used in a count if formula to see how many times each specific intersection has been responded to (the answer goes into column B)
- This intersection number is then translated into the intersection name using 25 nested if/then formulas (column L)
- Once the intersection count in column B is found, and the intersection name in column L is established, the specific intersection count can be found. This is found using a VLookup. The formula tries to find the intersection name of the response from column L and match it to the intersection name in column A. It then returns the count of that intersection found in column B. The final answer is then inserted into column I
- Finally, the intersection count found in column I is translated into a word based on the number of times this course is on the grid. Currently, these words are low, medium, and high and that value then gets translated into column K
As you can see, a LOT of formulas and behind the scenes work is going on here. I love it because to me it is like a giant interlocking puzzle. This puzzle can be incredibly frustrating when one simple thing isn’t working and can seem like a lot of work for such a simple end result (see below), but for some reason, I continue to fall in love with this kind of work.
2. How Complicated this Simple Visual Actually Is
As you read earlier, there are a lot of puzzle pieces of formulas put together to make overall a pretty simple visualization. However, each of these formulas is necessary to make the grid happen. Here is an easy breakdown:
- The engagement and technology numbers let the bubble know where to be placed on the grid
- The intersection number is used to set the bubble size, but to get the number 4 different formulas are necessary
- Lastly, the number field sets the color of the bubble to finish out the look of the grid
Besides the actual grid, I added the title and description along with the axis names.
The other major hurdle I recently had was trying to eliminate the data labels when a bubble was hovered over. The problem with these labels was they showed ALL of the information that was needed to place the bubble. This meant five pieces of information were shown. Some of these pieces of info were helpful like the Intersection Name and Count, but the rest was unnecessary. We decided the easiest thing would just be to get rid of it. There was an easy solution of putting a transparent rectangle over the top of the grid so when a mouse was on top of the grid the mouse was really hovering over the rectangle, not the grid.
3. How Easy it is to Become Discouraged
This grid has been almost a year-long process since the creation of the idea.
There were many problems I had absolutely no idea how to feel and it required me to look either on the internet or ask for help from those around me.
Even thinking of the formulas needed to get that information was a mental puzzle. I enjoy this type of project when it works (obviously), but sadly most days this isn’t the case.
There were many little obstacles on the road to this seemingly simple project. Many of these things I was overthinking, others I was just thinking about in a very different way.
Honestly, some days I look at my work and am impressed at how far the project has come. Meanwhile, others I feel saddened that it has taken this long to figure out something so “simple” looking back. There were many days that I felt so unmotivated to work because I would just stare at the screen and have no clue how to fix the problems staring back at me. I know this type of work is a process, but so frequently I feel like I have to know how to immediately do everything.
Overall, I have learned an incredible amount over the course of this project. The major lesson I have learned is how important it is to push through when you are stuck but also taking breaks is important. Also as much as I like to think that I have to know everything, I am thankful I have strong support around me and that I can always reach out when I am stuck.