I’ve been asked about my favourite Excel function several times. I usually claim that it is OFFSET() because it gives me the chance to pretend to be Johnny Depp when I explain how it works. Of course, OFFSET() is not without its issues. It is a volatile functions which means it is calculated whenever any recalculation is triggered in the Excel workbook, unlike ‘normal’ functions which are part of a calculation chain, so only recalculate when a precedent item in the chain changes. As a result, using a large number of volatile functions can lead to slower recalculation times. OFFSET() breaks the calculation chain in another way. The Excel Formulas Ribbon tab, Formula Auditing group, Trace Precedent and Trace Dependent options can’t trace a reference through an OFFSET() function. A recent post showed how the non-volatile INDEX() function can replace the use of OFFSET() for certain types of operation as well as explaining how each of the two functions works (please note this post is only available to IT Faculty and Excel Community members):
INDEX() v OFFSET()
Anyway, a few days ago, my daughter asked me, not what my favourite function was, but what my favourite formula was (just so you know, as a family we don’t actually talk about Excel all the time). This took a bit more thought. I’ve come across some pretty fearsome formulae in my time and have even written a few, but I was determined to come up with something that looked quite simple – so not one that included several levels of nested functions. I also thought it would be good to come up with a formula that incorporated a superpower function.
Several Excel functions appear to be modest and relatively nondescript, but deep inside hide the ability to perform extraordinary feats.
For example, MOD(). MOD() couldn’t be any more ordinary really. It takes two arguments: a number and a number that that number will be divided by. MOD() returns the remainder (or modulo) of the division. Although this might not sound very promising, MOD() can be used to recognise, and act on, patterns. For example, you could use MOD() to automatically included periodic payments in a cash flow forecast. Assuming a quarterly standing order that is paid in March, June, September and December, it would be possible to use MOD() to calculate the remainder when each month number is divided by 3. Only for March, June, September and December will the remainder be 0. This example, uses IF() with MOD() to include the standing order value ($D3) at the designated frequency ($C3):
Our IF() result is multiplied by another calculation. This calculation returns a TRUE or FALSE result and is designed to multiply our result by 1 if the start date ($A3) is on or before the month end date for the current column (E$1), but multiply it by 0 if it is not.
Doesn’t return 1 or 0, it returns TRUE or FALSE. A process known as ‘coercion’ converts TRUE to 1 and 0 to FALSE if Excel knows that we want to perform a mathematical operation. In this case it is the multiplication operator that causes Excel to coerce the result of our comparison calculation to be a number.
Back once again to my favourite formula. It’s not the MOD() formula although it does use another superpower function and it does make use of the same concept of using TRUE and FALSE as 1 and 0:
Let’s explain the superpower first. Like MOD(), SUMPRODUCT() seems quite trivial, it just multiplies its arguments and returns the total. For example, assuming A1 contains 2, A2 contains 3 and A3 contains 4:
=SUMPRODUCT(A1,A2,A3) = 2*3*4 = 24
The SUMPRODUCT() superpower is that it treats its arguments as arrays. This means that if one of the arguments contains multiple values, the calculation will be performed for each individual value. For example:
=SUMPRODUCT(A1:A3,A1:A3) = (2*2)+(3*3)+(4*4) = 29
In my favourite formula, SUMPRODUCT() is really only performing a simple SUM() operation, its main use is to act as a ‘wrapper’ to ensure its arguments are treated as arrays as described above.
The formula is looking at pairs of cells, one pair at a time. It starts by seeing whether B2 contains an x. If it does the statement is TRUE. It then checks whether C2, the cell immediately to the right of B2, contains anything other than an x. If it does that statement is TRUE. This means our formula has four possibilities for each pair of cells:
Our multiplication operation coerces our TRUEs and FALSEs to 1s and 0s. The only possibility that returns a 1 rather than a 0 is:
TRUE, TRUE or 1*1=1
All the other possibilities include at least one 0, so end up as 0 overall.
By adding all the calculations that result in 1, the formula counts the number of cells between B2 and AA2 that contain x themselves but are not to the left of another cell that also contains an x. Here, we use the formula to calculate the number of periods of absence for some employees:
That’s the formula that first came to mind as my favourite because it is deceptively simple, it uses a superpower function together with Boolean algebra and does something practical.
Please add a comment to let us know what your own favourite formula is and why. No actual prize, but a potential place in our ‘favourite formula hall of fame’ awaits.
This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.