I was asked several times about my preferred Excel function. I usually say it's OFFSET () because it gives me the ability to pretend to be Johnny Depp if I explain how it works. Of course OFFSET () is not without problems. It is a volatile function, ie it is calculated when a recalculation is triggered in the Excel workbook, in contrast to "normal" functions that are part of a calculation chain. Therefore, only recalculate them if a previous element in the chain changes. As a result, using a large number of volatile functions can result in slower recalculation times. OFFSET () interrupts the calculation chain in another way. The "Excel Formulas", "Formula Monitoring Group", "Track Precedent" and "Track Dependent" tabs cannot track a reference using an OFFSET () function. A recent post showed how the non-volatile INDEX () function can replace the use of OFFSET () for certain types of operations and explained how each of the two functions works (please note that this post is for IT faculties and Excel only is available to community members):
INDEX () v OFFSET ()
However, a few days ago my daughter asked me not what my favorite function was, but what my favorite formula was (just so you know that as a family we don't always talk about Excel). This required a little more thought. I came across some pretty scary formulas and even wrote some in my time, but I was determined to find something that looked fairly simple – not one that contained multiple levels of nested functions. I also thought it would be good to develop a formula that contains a superpower function.
Excel super power functions
Some Excel functions seem humble and relatively inconspicuous, but deep down hides the ability to do exceptional work.
For example MOD (). MOD () couldn't be more ordinary. Two arguments are needed: a number and a number by which to divide that number. MOD () returns the remainder (or modulo) of the division. While this may not sound very promising, MOD () can be used to recognize patterns and respond to them. For example, you can use MOD () to automatically include periodic payments in a cash flow forecast. Assuming a quarterly standing order paid in March, June, September and December, it would be possible to calculate the rest using MOD () if each month number is divided by 3. For March, June, September and December only, the rest is 0. In this example, IF () is used with MOD () to include the standing order value ($ D3) with the specified frequency ($ C3):
= IF (MOD ((E $ 2- $ B3), $ C3) = 0, $ D3.0) * ($ A3 <= E $ 1)
Our IF () result is multiplied by another calculation. This calculation returns a TRUE or FALSE result and is used to multiply our result by 1 if the start date ($ A3) is on or before the end of the month for the current column (E $ 1). If so, don't multiply it by 0.
$ A3 <= E $ 1
Does not return 1 or 0, but TRUE or FALSE. A process called "constraint" converts TRUE to 1 and 0 to FALSE when Excel knows we want to do a math operation. In this case, it is the multiplication operator that causes Excel to force the result of our comparison calculation as a number.
Back to my favorite formula. It's not the MOD () formula, although it uses a different superpower function and uses the same concept of using TRUE and FALSE as 1 and 0:
= SUMPRODUCT ((B2: AA2 = "x") * (C2: AB2 <> "x"))
First, let's explain the superpower. Like MOD (), SUMPRODUCT () seems to be quite trivial, it just multiplies its arguments and returns the sum. Assume A1 contains 2, A2 contains 3 and A3 contains 4:
= SUMPRODUCT (A1, A2, A3) = 2 · 3 · 4 = 24
The superpower SUMPRODUCT () is that it treats its arguments as arrays. That is, if one of the arguments contains multiple values, the calculation is performed for each individual value. For example:
= SUMPRODUCT (A1: A3, A1: A3) = (2 · 2) + (3 · 3) + (4 · 4) = 29
In my favorite formula, SUMPRODUCT () really does just a simple SUM () operation. Its main use is to act as a "wrapper" to ensure that its arguments are treated as arrays as described above.
The formula looks at pairs of cells, one after the other. First, it is checked whether B2 contains an x. If so, the statement is TRUE. It is then checked whether C2, the cell immediately to the right of B2, contains anything other than an x. If so, this statement is TRUE. This means that our formula offers four options for each cell pair:
Our multiplication operation forces our TRUEs and FALSEs to 1s and 0s. The only way that returns a 1 instead of a 0 is:
TRUE, TRUE or 1 * 1 = 1
All other options include at least a 0, so you get 0 in total.
By adding all the calculations that lead to 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 times employees are absent:
This is the formula that first came to mind as my favorite because it is deceptively simple, uses a superpower function along with Boolean algebra, and does something practical.
Please add a comment to let us know what your favorite formula is and why. You don't expect an actual price, but a potential place in our “favorite formula Hall of Fame”.
This blog is brought to you by the Excel community Here you will find additional blogs, extended articles and webinar recordings on various Excel topics. In addition to live training events, Excel Community members have access to on 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.
(tagsToTranslate) INDEX () (t) OFFSET () (t) MOD () (t) SUMPRODUCT () (t) Absenteeism (t) Quarterly standing orders (t) Best Excel formula