In this Google Workspace Top Tip I look at the power of ‘IF’ and how you can utilise it to “Automate the Boring”.
‘IF’ is probably the most powerful word in the Encompass Innovate dictionary.
Often heard with its close friends ‘what’ and ‘we’, together they drive innovation. They help us to identify new solutions to problems, or to roll out new improved processes to different teams.
Using ‘IF’ in Google Sheets
‘IF’ is also an incredibly useful function in Google Sheets. It takes the guesswork out of what your data is showing you.*
In its simplest form ‘IF’ returns one value if a logical expression is ‘true’ and another if it is ‘false’.
There are a number of ways this can be used in your spreadsheet, all of which can result in a ‘yes’ or a ‘no’ or any other word or phrase you wish. For example:
- More than 7 days since receipt
- Less than 7 days till target
- Budget available in project
We will revisit ‘IF’ along with its brothers ‘Countif’ and ‘Sumif’ as well as their operators ‘And’ & ‘Or’ in future posts. Let me know in the comments below how you have used ‘IF’ in your spreadsheets.
Using ‘IF’ to “Automate the Boring”
Something else that helps drive innovation at Encompass Innovate is our aim to automate the boring. The ‘IF’ function is great but it still requires you to go in and manually check on the spreadsheet. What if we (there’s our friends – working together) could automate the process so that you were informed via email of the status of your data?
This spreadsheet uses data copied from the London Stock Exchange around two years ago. Although it uses the GoogleFinance function the real leg work is done by a simple ‘IF’ function:
This translates to:
‘IF’ the current price is less than the purchase price – notify me.
The script works its way down the table stopping when it finds a “YES” and then collects the name of the company.
To amend the script, all you need to know is:
- The ‘IF’ statement is currently in column H (amend line 19 of the script)
- The company name is Column B (amend line 22)
Finally, don’t forget to:
- Change the email address on line 28
- Change the URL to your spreadsheet on line 35
The spreadsheet also walks you through how to set up the trigger to automate the email, just click on the “Setting up” sheet.
Click here for a downloadable version of “How to use ‘IF’ to Automate the Boring”.
How have you “Automated the Boring” or what would you love to be able to automate? Let me know in the comments below and make sure to come back as we continue this series of Google Workspace Tip Top Tips.
*btw I am also a grammar pedant but I draw the line at data are showing
Please reach out to see how we can assist you with Google Workspace training for your organisation.