How to test performance of a Google sheet formula?

Posted on

Whether you’re just starting out with Google Sheets or are a seasoned pro, sooner or later one of your formulas will give you a formula parse error message rather than the result you want. However, every Formula user, irrespective of whether it’s a beginner or expert, has invariably come across a formula parse error in Google Sheets at least once in their life (and if you haven’t. you soon will). It can be frustrating, especially if it’s a longer formula where the formula parse error may not be obvious. In this article, we will talk about some common formula parse errors in Google Sheets, like How to test performance of a Google sheet formula?, and how to fix it.

I was wondering, whether ARRAY_FORMULA() enclosed by COUNT() work much slower than COUNTIF() without ARRAY_FORMULA(). How to test/compare performance of formula that uses a range as parameter?

Answer :

I created this test spreadsheet the following way. I created a new spreadsheet, then deleted all columns but one, and then added as much rows as Google allowed me (between 4 and 5 million rows).
Then I added the following formulas:

1 | =SECOND(NOW()) & " | " & COUNT(ARRAYFORMULA(IF(A$8:A, 1, 1))) & " | " & SECOND(NOW())
2 | =INDEX(SPLIT(A$1," |"),3)
3 | =INDEX(SPLIT(A$1," |"),1)
4 | ="Total execution time in seconds: " & IF(A3-A2 >= 0, A3-A2, A3 + 60 - A2)

Now I caused recalculation of the formulas, by entering 0 in cell A8. I entered and removed “0” in that cell several times to see that if the results were the same or different. After each changing of A8 I had to wait for formulas to recalculate. Every time the formulas were recalculated, it recorded the total time of calculation in A4. I replaced COUNT(ARRAYFORMULA(IF(A$8:A, 1, 1))) with other formulas to compare performance of different formulas.

It is interesting the second NOW() is calculated before the COUNT() and the first NOW() is calculated after the COUNT(). I discovered this by manually checking the time, when re-calculation started and finished with the windows clock and comparing it with the results in A1, A2 and A3

This is some test results I obtained. I executed the test 8 times for each of the two formulas and calculated average result.

Formula                | Execution time, seconds, attempts 
                       |  1  2  3  4  5  6  7  8  AVERAGE
    IF(A$8:A, 1, 1)))  | 18 18 17 17 17 16 18 19  17.50 sec
COUNTIF(A$8:A, "")     | 18 18 18 20 18 18 18 18  18.25 sec 

Leave a Reply

Your email address will not be published. Required fields are marked *