Running Total Array Formulas (using the MMULT function)

In this post we’ll look at how to calculate a running total, using a standard method and an array formula method. We’ll cover the topic of matrix multiplication (take a deep breath, it’s going to be ok!) using the MMULT formula, one of the more exotic, and challenging formulas in Google Sheets.

If you like video tutorials, here’s the one on MMULT:

This is a lesson from my latest, Google Sheets course on Advanced Formulas 30 Day Challenge (it’s free!).

Continue reading Running Total Array Formulas (using the MMULT function)

10 techniques to use when building budget templates in Google Sheets

It probably won’t surprise you to hear that I use Google Sheets to build financial/budget templates and track my incomings and outgoing, both at home and for my business.

The dashboards available through online banking sites are pretty rudimentary and don’t give me much insight into what’s happening with my finances, particularly over longer time frames.

I like using Google Sheets, as opposed to another third party service like Mint, because it’s fully customizable, it’s easy to use and I can share any spending or budget templates easily with my wife.

Credit Card budget template with Tiller

I’m not a financial expert, so I won’t be dispensing any financial advice here. I won’t opine on what you should or shouldn’t show in your spending and budget templates in this post, nor will I talk about what your financial goals should be or how to get there.

What I will do in this post however, is show you some useful techniques in Google Sheets that you can use for building your own budget templates. Techniques to make them more insightful and more helpful for reaching your goals. They are:

  1. Why are you doing this?
  2. Invest time little and often
  3. Get basic formulas dialed
  4. Leverage power of more advanced formulas
  5. Don’t reinvent the wheel! Google has specific financial formulas
  6. Use comments to record specific details
  7. Get all your financial data into your Google Sheet with Tiller
  8. Set budgets and highlight spend over the budgeted amount
  9. Build drop-down menus to show different categories in your reports
  10. Use words!

Continue reading 10 techniques to use when building budget templates in Google Sheets

Filtering with dates in the QUERY function

If you’ve ever tried to filter on a date column in the Query function in Google Sheets, then you know how tricky it can be.

In a nutshell, the problem occurs because dates in Google Sheets are actually stored as serial numbers, but the Query function requires a date as a string literal in the format yyyy-mm-dd, otherwise it can’t perform the comparison filter.

This post explores this issue in more detail and shows you how to filter with dates correctly in your Query formulas.
Continue reading Filtering with dates in the QUERY function

Show data from the GitHub API in Google Sheets, using Apps Script and Oauth

This post shows you how to connect a Google Sheet to GitHub’s API, with Oauth and Apps Script. The goal is to retrieve data and information from GitHub and show it in your Google Sheet, for further analysis and visualization.

If you manage a development team or you’re a technical project manager, then this could be a really useful way of analyzing and visualizing your team’s or project’s coding statistics against goals, such as number of commits, languages, people involved etc. over time.

Contents

  1. What are Git and GitHub?
  2. Access GitHub API using Basic Authentication
  3. Access GitHub API using OAuth2 Authentication
  4. Resources and further reading

Note, this is not a post about integrating your Apps Script environment with GitHub to push/pull your code to GitHub. That’s an entirely different process, covered in detail here by Google Developer Expert Martin Hawksey.

Continue reading Show data from the GitHub API in Google Sheets, using Apps Script and Oauth

How to add a total row to a Query Function table in Google Sheets

This article looks at how to add a total row to tables generated using the Query function in Google Sheets. It’s an interesting use case for array formulas, using the {...} notation, rather than the ArrayFormula notation.

So what the heck does this all mean?

It means we’re going to see how to add a total row like this:

How to add a total row to a Google Sheets QUERY table
Table on the left without a total row; Table on the right showing a total row added

using an array formula of this form:

= { QUERY ; { "TOTAL" , SUM(range) } }

Now of course, at this stage you should be asking:

“But Ben, why not just write the word TOTAL under the first column, and =SUM(range) in the second column and be done with it?”

Well, it’s a good question so let’s answer it!

The reason for using this method is because the total line is added dynamically, so it will be appended directly at the end of the table, and won’t break if the table expands or contracts, if more data is added.

It’ll always move up or down, so it sits there as the final row.

Continue reading How to add a total row to a Query Function table in Google Sheets