Approach
A function refers to a standard built-in operation such as SUM, AVERAGE, etc., and a formula is your creation, as you apply and combine functions with references and values. Formulas are a strong dialect using the function vocabulary. The syntax is more familiar than VBA code, but still far from natural fluency for novices.
A formula can be as simple as =A1 or as long as 8,192 characters. They can be nested and combined in unlimited ways that as you have probably seen, can quickly become unintelligible. Actually it is not quite unlimited, according to an Office tip, “Nest no more than 64 functions”, to which I reply, “are you kidding me?!”. The risk is more than an affront to fluent users. Complex formulas can become unsustainable to the point where you can’t explain their logic even to yourself. The risk of “I don’t understand the process” quickly leads to “I don’t trust the result.”
You do not want to perform surgery to try to “cure” poorly constructed formulas, so prevention is important. Before you get tongue-tied in formula-speak, think about basic communications. We converse effectively in short sentences with subjects, verbs and objects. Apply this principle to formulas and they won’t get out of hand. Let’s look at techniques:
Function Choice
Get to know functions well enough so that you don’t get stuck trying to make do with the obvious few that you first learn. With each new application, research the list and look for the best one or combination for your purpose.
Pick any empty cell and type “=a” to see a list of functions beginning with a. Backspace and type b, then c, and so on. Arrow down through each list to see function descriptions. Tab on one to select it and open the list of arguments. This method gets you familiar with the function names and also how to enter them efficiently, directly in the cell.
Next, browse functions by category, by clicking fx in the formula bar or via Shift+F3. This gives you explanation of the arguments, and a link to a help page to complete your understanding.
There are over 100 functions ranging from the common SUM to esoteric financial and engineering operations. You can quickly scroll through many you’ll never use, and frequently revisit those that look interesting. Think through your purpose and strategy before you start.
One of the most common needs is look ups, for example, to link two tables. There are several ways, and many articles written on their virtues; VLOOKUP, HLOOKUP, the newer XLOOKUP, DGET, or a combination of INDEX and MATCH. This is an example where you need to evaluate and make your own choice, then master it.
A common novice attempt to manage complexity is the nested IF. It only takes a few levels to get lost in the logic. This can be avoided with CHOOSE, or by INDEX of a range or array constant. As a rough guide, if you need more than two IFs, try CHOOSE, and if you need more than four or five, use INDEX with a lookup table.
Consider the following, where two array constants are defined for input and output, and we need to return the correct output for each input.
InputList ={"a";"b";"c"}
OutputList ={"alpha";"bravo";"charlie"}
The cell named Selection is where the user enters one of the three input values. There are at least three ways to get the desired output from the selected input:
=IF(Selection="a","alpha",IF(Selection="b","bravo","charlie"))
=CHOOSE(MATCH(Selection,InputList,0),"alpha","bravo","charlie")
=INDEX(OutputList,MATCH(Selection,InputList,0))
The best choice will depend strongly on the complexity of the input and output lists, and the possible need to expand and maintain them. If you hard-code the choices into IF or CHOOSE, you must find and edit all such formulas if the list changes. Further, if you don’t limit inputs with Data Validation, you must have an “or else” clause for unexpected values. A lookup table makes the process transparent and change-ready, and with a range name such as InputList = Table[Input], can double as Data Validation.
Modular Formulas
Long formulas can be broken down into stages to make them more readable and more portable. This is particularly helpful if the intermediates are used in multiple output formulas, so that you can edit and audit the part once for many. It also helps just for comprehension, like learning to read in syllables. If you get to the point where you are counting parentheses in your formula, think about splitting it into stages.
Intermediate formulas can be written on the sheet, if it aids transparency, or hidden in Name Manager. Beware of getting too granular with components, this can lead to frustration as the logic becomes a treasure hunt.
Two projects in particular illustrate the benefit of modularizing. Startup uses step-by-step formulas in Name Manager to break apart the complex calculation of Holidays. The MRP Demo uses a table of intermediate calculations to illustrate MRP logic, as explained in the Formulas section.
Named Arguments
Operations within a table record, that is, in the same row, automatically use structured reference. If you refer to another row, such as one up or one down, it reverts to the default cell address reference. You can maintain structured reference by using OFFSET, or use a relative range name. See the example below.
Outside of a table, create fixed or relative range names for ranges that you perform operations on. See the section on Name Manager for more information. Names go a long way to improve transparency.
Self-Documentation
The formula bar allows for spaces and line breaks and even comments to improve transparency. The function N() converts any text to the number 0. This means you can add it anywhere in a numeric formula to explain the step by step process without affecting the calculation. Consider the following example:
= OFFSET([@Inventory],-1,0) + N("previous inventory...") +[@Demand]+[@Supply] + N("...plus net change = ending inventory")
The formula is written on two lines for legibility, using Alt+Enter to create the line break, and each line ends with + N(“explanatory text”) for that stage of the formula. Formulas with several lines may be more readable with appropriate indentation on each line. To help users read multi-line formulas, you can start the first line with N(“F2 or Ctrl+Shift+U to read the formula”).
Logical Formulas
Boolean or binary logic is the DNA of computer hardware and software, yet we seldom see it in its raw form. Ever since CDs replaced audio tape we’ve known that what we hear is made up of zeroes and ones, but it’s hard to grasp how that’s possible. We would need a super-microscope to see the individual bits of the digital content we consume. Boolean logic is nonetheless an important element of spreadsheet design.
A variable refers to something whose value can vary, as opposed to a constant which has only one value. Variables that can only ever have true or false values are called Boolean. The value itself might be referred to as a state or a condition. Functions that return or use Boolean values are called logical functions.
An easy way to think about logical conditions is to compare a variable with many values to a constant. Age can have over a hundred values, but age > 40 can only be true or false. Some logical functions take Boolean input and return variable output. For example, IF (Age > 40, Then, Else) has two output values based on the Boolean input condition.
Boolean conditions are essential elements for building powerful formulas. They are also the basis for two important features, Conditional Formatting and Data Validation. These features allow you to construct the Boolean logic in various ways, or to use a formula that returns a Boolean value, then set the effect of that value.
In Conditional Formatting, you define the effect you want based on a TRUE condition. The condition identifies records that you want to format specially, to bring attention to them. In Data Validation, you define the effect you want based on a FALSE condition. The condition defines what values are valid, for which you want no effect. When the condition is FALSE the value is invalid, and you define whether to reject it or allow it, and what message to give.
To learn to use logical functions, set up two cells, A1 and A2, with values 0 and 1. If you evaluate them with logic, =IF(A1,TRUE) returns FALSE and =IF(A2,TRUE) returns TRUE. In fact, zero is always FALSE and any non-zero number evaluates as TRUE.
If I want to know if both cells are TRUE, logically I can use =AND(A1,A2), or mathematically I can multiply them, =PRODUCT(A1,A2), or =A1*A2. If either cell is 0 (FALSE), AND() and PRODUCT() are FALSE. Only if both operands are TRUE can the result be TRUE. I can also count zeroes in the range and flip the result – if there are any zeroes then the result should be FALSE, as in =NOT(COUNTIF(A1:A2,0)).
Similarly, to find if either cell is TRUE, I can use =OR(A1,A2), or by math =SUM(A1,A2), or =A1+A2. In this case, only if both operands are FALSE can the result be FALSE. To use count to find if there are any non-zeroes I use =COUNTIF (A1:A2,”<>0”). Note that COUNTIF() is used here to determine existence rather than actual count. To summarize the logical equivalencies:
PRODUCT (refs) = AND (refs) = NOT (COUNTIF (refs,0)) = “all TRUE” SUM (refs) = OR (refs) = COUNTIF (refs,“<>0”) = “any TRUE”
Note that if the data include negative numbers, SUM does not always equal OR, unless ABS is added. ABS accepts only a single argument, which can be a range.
=SUM (-1,1) = 0 = FALSE, whereas OR (-1,1) = TRUE. =SUM (ABS(range)) = 2 = TRUE, where range contains -1 and 1
The Information function category has several Boolean functions with names beginning with “IS”, indicating the binary result, “it is or it isn’t”. They report the type of data in the reference cell, including error trapping functions. N() and LEN() are ways to convert text to number or logical, but do not handle errors. Shaded cells in the table below evaluate FALSE.
Notice the relationships: ISTEXT() is the opposite of ISNONTEXT(); besides errors, LEN() is the opposite of ISBLANK(); ISERROR() is the equivalent of OR(ISNA(), ISERR()), and can be used as an outer wrap on a formula to catch any error.
For convenient error handling, ISNA() and ISERROR() each have special versions of IF(). This avoids the duplication of the output formula required for standard IF().
A common example of IFNA is used with MATCH. When the lookup value is not found in the lookup array, MATCH returns #N/A. To show this condition as a blank or as “not found”, enter it as the IFNA result: =IFNA(MATCH(lookup, range,0),”not found”).
Commentaires