Google Sheets: ARRAYFORMULA, IFERROR, SPARKLINE, Conditional formatting, workflow.
This is Part 2 of this Google Sheets Intermediate Project. If you are looking for part one, you can get it here:
This project compares two currency exchanges rates, USD-XRP, in two different exchange gateways Bitstamp and Gatehub.
Contents
- Recap
- Stage 2
- Highest Exchange Rate Gateway
- Average Day Change
- Sparkline
- Finishing Touches
- Conclusion
Recap
In the first part of our tutorial, you made a copy of the raw Exchange Volume data. Then in a new Google Sheet tab called Tutorial, you made a column of UNIQUE Date Time Stamps.
Realising that this was not user-friendly you grabbed just the date data from Column A and put it into Column B using the LEFT function. You then used some conditional formatting to highlight every Sunday automatically.
Next, you added two columns to display the daily exchange rates for the two gateways using FILTER. You then used two colours to identify which gateway had the highest or lowest exchange rate for that day.
You then grabbed the average of the two gateways before displaying their percentage difference.
By this stage everything looks like this:
Stage 2
In this tutorial, we are going to make it even easier for our reader, by setting up a column to display the gateway name of the highest exchange for each day. We’ll then put in a block of columns that will display data about the day change of the average of the two gateways. Finally, we will add a SPARKLINE graph into each row so the user can, at a glance, see how the average exchange has been tracking for the past 7 days.
By the time you are done your chart should look like this:
Highest Exchange Rate Gateway
The Header
You should be up to column G in your Tutorial Google Sheet by now. Merge G1:G2 and then add the header below:
Highest Exchange Rate Gateway
You’ll need to apply wrap and format that just like you did with the header in Column F. You can do this super quick by applying the Paint Format button. Just click F1, then click the Paint Format Button to select the format of that cell before clicking G1 to apply that same formatting.
Determining the gateway with the highest exchange rate for the day
To figure out which gateway has the highest exchange rate for the day and then display that gateway’s name we are going to use a simple IF function.
All we need to do is compare the daily value of Gatehub in Column C against the daily average in Column E. If Gatehub’s daily exchange rate is higher than the average, it is the highest exchange rate, otherwise it will be Bitstamp. We can draw the names from the headers in row C2 and D2.
Here is how the formula would look for the first row:
=IF(C3 > E3,$C$2,$D$2)
Or in quazi-code:
=IF(Gatehub exchange rate > average,Select Gatehub,Otherwise select Bitstamp)
You can see above in the formula, that I have locked in the two name reference cells for Gatehub and Bitstamp using the Absolute Reference dollar signs($). I just punched in the shortcut, F4 on the keyboard, to quickly do this.
At this stage, I would test out the code on the first cell to make sure it is doing what I want it to. If I am satisfied, it is time to bring out the ARRAYFORMULA.
Have another go at applying the ARRAYFORMULA to this so that it is applied to every row down the page. Don’t forget to take into account empty cells.
…
Success?
Our next step in our workflow would be to test the ARRAYFORMULA. It would look a little like this:
=ARRAYFORMULA(IF(C3:C > E3:E,$C$2,$D$2))
Now we have the full range from the third row onwards.
Once satisfied that this is working as planned, we would then hide any errors where there is no data. I’ll use a nested IF statement this time around.
To refresh from Part 1 of this tutorial, I need to state that IF the is a blank in the cell in column C then leave this cell blank. Otherwise, run the formula.
=ARRAYFORMULA(IF(C3:C = "","",IF(C3:C > E3:E,$C$2,$D$2)))
Conditional Formatting
While having the name of the gateway with the highest exchange rate each day is super helpful for the user, we can create a more efficient user experience (UX) by colour coding the gateway names.
Do you remember in Part 1, where we decided to add colour to the header of each gateway name that was in cells C2 and D2? Well, we are already doing some mad psychological mind games with our users.
The user, looking at those two green and purple headers, has automatically patterned those colours to those Gateway names. Now we have them referencing those colours, let’s use them again to highlight the gateways.
Go into your conditional formatting again: Format > Conditional formatting… .
This time we aren’t doing anything fancy. First, apply the range to G3:G1000 or more. Then under the Formula rules drop-down select, Text is exactly.
An input will appear. Here type in Gatehub. Apply the same formatting that you did for the header in C2. Mine was a light green background with a dark green text that was bolded.
Once satisfied, got to the very bottom and select Add another rule. Change the test to Bitstamp and apply the matching purple to your D2 header. Lastly, select Done.
Everything should look like this:
Note the conditional formatting and the formula.
Back to Top
Average Day Change
It’s usually pretty important to see how much an exchange rate has changed day-to-day. So much so, that we are going to use the next three columns to describe this.
The first column will be the average XRP exchange rate change in USD from the previous day to the current day. Then we will display the percentage of change in the next column. Finally, we will help up our user readability game by providing an up arrow symbol ↑ and a down arrow symbol ↓ to really bring home whether the rate has risen or fallen compared to the previous day at a glance.
By the time we are done these three columns are going to look like this:
Headers
Add Average Day Change to H1:J1, merging the cells. Then apply $ to H2, % to I2 and ↑ or↓ to J2 as subheadings.
Back to Top
Column H: Day change in USD
You can probably see in the first cell that each of the rows is marked with an “N/A” for “Not Applicable”. This is because there is no previous day’s data for the first cell in Row 3 so we have nothing to compare against.
As such, we start our calculations on Row 4.
Go in and update H3:J3 with “N/A” now.
To get the day change of the average exchange rate from the previous day to the current day, we simply subtract the current day from the previous day.
Exchange rate change = current day - previous day
Here we are referencing column E, the Average.
=E4-E3
Of course, we can apply the ARRAYFORMULA function to this too.
Try applying this now along with adding a nested IF statement to hide any errors or unwanted zeroes on empty spaces.
…
Did you have a problem trying to test your E4:E-E3:E formula inside the ARRAYFORMULA before taking care of the empty cells? You might have experienced this error:
Error
Result was not expanded automatically, please insert more rows (1).
No huge issue here. What is happening is that E3:E is always going to be one larger than E4:E. So when you run the formula, it is essentially trying to apply the E4:E to a range that is always one more than the maximum length E3:E, perpetually always being one row move than what the sheet has. Confusing, I know. Just remember that it causes problems.
Note: This may have also played a bit of havoc with your displayed row depth, which can chew up ram and slow down your Google Sheet. I recommend that you delete that formula and then delete all the excess rows up to say one or two thousand (You’ll see that they probably went all the way down to 50,000+ rows).
Now that we have learnt one of the pitfalls of ARRAYFORMULAS, let’s go ahead and fix it up with an IF statement to take into account blank rows.
=ARRAYFORMULA(IF(E4:E = "","",E4:E-E3:E))
Now the formula won’t be run when there is no data, so it won’t cause the error above.
Back to Top
Column I: Daily Percentage Change
Another good indicator of the daily exchange rate change is to see the rate of change as a percentage. To get the percentage rate of change we subtract the present day’s value from the previous day’s value and then divide that by the present day’s value.
% change = (present value - previous value)/present value
We have already subtracted the present value from the previous value in Column H and we can get the present value from our Average Column again in Column E. So, for our first cell, cell I4, our formula would look like this:
=H4/E4
Applying ARRAYFORMULA and checking for empty cells again, our formula would look like this:
=ARRAYFORMULA(IF(H4:H = "","",H4:H/E4:E))
We’ll now have a column with numbers with some really long decimal places, which is hard to read at a glance. Transform those into a percentage with the % button and make sure there are only two decimal places.
Column J: The UP-Down Arrow Column
When you look at exchange changes online you often see the up and down arrows. This is a really good visual queue for the user to see which direction the change went. We’ll do the same.
In Column J, all we need to ask is that if the average day change (Col H) is greater than zero then we want an up arrow ↑. Otherwise, we want a down arrow ↓.
It’s actually a bit of a pain to add symbols to a Google Sheet. For me, on Windows 10 I used the Character Map (Character Pallete for MAC) to find the up and down arrows and then copy and paste them into a spare cell while I was working out the formula.
I just searched for it in my Windows search bar.
The arrows are towards the bottom of the page. You can select them and then place them in a spare cell. (You could also just select these ↑↓ and paste em in 😉 ).
Back to our formula…
So now our formula for the first line should look like this:
=IF(H4 < 0,"↓","↑")
Again the ARRAYFORMULA and a nested IF statement to take care of empty cell reference can help us complete the calculations for the rest of the rows.
=ARRAYFORMULA(IF(H4:H ="","",IF(H4:H < 0,"↓","↑")))
Great!
All three columns are complete. Your Tutorial Google Sheet should look like this:
Conditional Formatting
Let’s take this user experience one step further and add a red fill to all three cells in a row if the day change is positive or a blue fill if the day change is negative.
Now, you might be tempted to use the greater than and less than options in the Conditional Formatting menu. However, these will only fill the cell they are referencing.
We can use the custom formulas to check column H and if it is positive or negative, change the colour of all 3 column’s, H, I and J.
Select the range from H4:J1000 and then jump into the conditional formatting sidebar.
When entering our sample formula, keep in mind that the sample always references the first most top-left selected cell. In our case, this is H4. If we were to lock column H with and absolute reference dollar ($) sign, then anything applied to that cell will be applied to the other selected cells in the same row.
So our formula should look like this for our positive values:
=$H4 > 0
And for our negative values:
=$H4 < 0
Again we applied a blue background to the positive values an a red to the negative ones.
Your Average Day Change columns in your Tutorial Google Sheet should now look like this:
7 Day Sparkline Graphs
One final thing that would be pretty useful would be to see a daily graph of the exchange rate change of the last 7 days. We’ll do this in our final column, column K.
Give it a header in K1:K2:
7 Day Performance at a Glance
Sparkline
We can generate these graphs daily by using the SPARKLINE function. SPARKLINE takes a range of data to transform into a chart in its first parameter. As an optional parameter, you can change the type of chart and it’s formatting. We’ll leave the optional parameter for another time and focus on how to use the basic SPARKLINE in our current project.
Our SPARKLINE chart data will be the data in our daily Average column, col E.
For our first day of data, we don’t have anything to compare. So we will just leave cell K3 as “N/A”.
For the next 6 days we want our SPARKLINE data to grow by 1. So on the second day we want our SPARKLINE to look like this:
=SPARKLINE($E$3:E4)
Now, there are two reference points of data for that day. This will create a single gradient line between the two points:
We’ve locked in the first cell reference here because we will drag this formula down for 5 more days. After that, we will have 7 or more days worth of daya so we will modify our formula.
Drag that formula down to K9 and you will be able to see that chart grow to take 6 days of data.
Once we hit row 10 we will change our formula to only take 7 day blocks of data. This means we will need to take off those absolute values.
=SPARKLINE(E4:E10)
Now the first argument will be free to increment by one just like the last argument when we drag the formula down the column.
But we don’t want to do that just yet. We don’t want to see the SPARKLINE chart or an error when there is a corresponding empty data row. So, let’s add an IF statement to cover that:
=IF(E10 = “”,””,SPARKLINE(E4:E10))
Next, go ahead and drag that formula down to the bottom of the sheet. ARRAYFORMULA is not going to work in this instance. You’ll just have to drag the formula down.
The end result should look like this:
Not in cell K10 where we have changed the formula now that there is enough data.
Back to Top
Finishing Touches
We are all but done. All the heavy lifting is over and all we need to do now is centre all our data and put a few boarders.
Go ahead and centre everything first.
Border design
Border design is pretty intuitive. We are going to put a thicker border around blocks of relevant information and then a thinner one around the rest.
When using thicker borders I find the standard black to be a little too intrusive. I usually go a darker grey one or two shades off the black.
As for workflow, we would first select the entire width of data and select all the way to the bottom of the sheet past the data – this is live data after all. To do this quickly I would use Ctrl + A to select the data range, then Ctrl + Shift + ↓ to also select through to the bottom.
Then we would change the border colour to dark grey and border everything in the thin border.
Next, we would go back and select the thick border and border around all the different blocks of data by selecting the whole columns.
Lastly, I would freeze the first two rows so when the user scrolls down, they don’t lose the header.
The final result should look like this:
Conclusion
Well, damn! You made it!
Good job!
You’ve covered a lot of ground since we started back in Part 1. Hopefully, a bit of repetition of the ARRAYFORMULA has helped you understand how it works and let you think about how you would apply it in your own project.
You also got a glimpse at my workflow. Learning the steps to try formulas and do things in what order can significantly improve your production speed.
We also took a look at SPARKLINE charts in the hope that it would light a …spark … in you (You didn’t think you would get away without a pun did you?).
If you want a sample of the whole project completed you can find it in the link here:
In our final chapter of this project, we are going to take the Tutorial Google Sheet we created and generate an Exchange Rate chart for the two exchange gateways.
Follow my posts on social media or subscribe to find out when that will be published!
I’m a huge fan of Justin Mares, Mastering Google Sheets course.*
Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.
~Yagi