Al sent me a formula to revise. Before:
=IF($B11<>0,IF(OR(ISNUMBER(FIND("-",J11))=TRUE,ISNUMBER(FIND(" ",J11))=TRUE,ISNUMBER(FIND("_",J11))=TRUE),
IF(ISNUMBER(FIND("-",J11))=TRUE,TRIM(SUBSTITUTE(J11,"-","")),IF(ISNUMBER(FIND(" ",J11))=TRUE,TRIM(SUBSTITUTE(J11,"
","")),IF(ISNUMBER(FIND("_",J11))=TRUE,TRIM(SUBSTITUTE(J11,"_","")),TRIM(J11)))),TRIM(J11)),"")
After:
=IF($B1<>0,TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J1,",","")," ",""),"-",""),"_","")))
What the formulas do and what they are supposed to do is not why I am sharing them with you. There are few techniques that can be demonstrated with these formulas that might help you become a better formula writer. This isn’t a comprehensive guide to writing formulas, just a few tips.
Formula Length
Making a formula shorter doesn’t necessarily make it better, but usually it does. I’d guess about 5% of the time, a longer formula is preferable to a shorter one. If the longer formula makes it easier to read and understand and doesn’t introduce volatility or undue calculation time, then it’s good. But verbosity for it’s own sake doesn’t appeal to me. If it’s shorter, that’s less stuff I have to keep in short term memory while I’m deciphering.
Long formulas are hard to read. I don’t care who you are or how long you’ve been writing Excel formulas, fifty character formula take time to understand (unless you wrote it). So if you get a workbook with some monster formulas, don’t feel bad that you can’t trace through it in no time.
Terminology
Formulas are anything in Excel that starts with an equal sign (=). Examples of formulas are =1+1, =A1+1, and =SUBTOTAL(9,A1:A100). Formulas, like the last one, can contain functions. Functions are the built-in Excel keywords like SUM, SUBTOTAL, IF, SUMIF, OFFSET, and many others. When you’re referring to those keywords, call them functions. When you’re referring to the whole schmear, call it a formula.
Nesting
Functions take arguments (sometimes called parameters) and spit out a return value. Arguments can be many things like numbers, strings, cell references, and even other functions. When a function is used as an argument in another function, it’s called nesting.
Not nested: SUBSTITUTE("Richard J. Kusleika", ".","") returns “Richard J Kusleika” and says ‘replace all the periods in the string with nothing (an empty string)’.
Nested: SUBSTITUTE(SUBSTITUTE("Richard J. Kusleika",".","")," ","") returns RichardJKusleika and says ‘replace all the periods in the string with nothing, then take that result and replace all the spaces with nothing’.
The result of the innermost SUBSTITUTE is the first argument to the outer SUBSTITUTE. Excel always solves formulas from the inside out.
Error Proof Functions
Some functions are error proof. SUBSTITUTE is one of them. =SUBSTITUTE("Some Text","blah","") will return “Some Text”. It replaces every instance of “blah” with nothing. It’s just that there aren’t any instances. But it doesn’t return an error, and you can use that fact to make your formulas leaner.
TRIM is another function that doesn’t error out. TRIM will remove any spaces from the start and end of a string. If there aren’t any spaces, it just returns the string. You can use TRIM with any string you want and not worry that an error will be returned.
Other functions like FIND do return errors. FIND returns the position of the string-to-find in the string-to-be-searched. If the string-to-find isn’t in there, an error is returned.
Knowing what functions return in error cases like this can help you write more succinct formulas.
Booleans
Booleans are TRUE and FALSE. If you’re testing whether something is TRUE, you don’t have to say IF(Something=TRUE,… because Excel knows how to evaluate whatever you use as the first IF argument. Instead of =IF(A1=TRUE, A2, B2) write =IF(A1, A2, B2)
Instead of =IF(ISERR(FIND("blah","Some Text"))=TRUE, "Not found", "Found") write =IF(ISERR(FIND("blah","Some Text")), "Not found", "Found")
When Excel expects a Boolean, it will do the conversion for you.
Patterns
Look for patterns in your formulas and try to call functions the fewest number of times. In the first formula at the top of this post, the TRIM function is called over and over. Whatever else this formula does, it could first be improved by moving the TRIM function to the outside:
=TRIM(IF($B11<>0,IF(OR(ISNUMBER(FIND("-",J11))=TRUE,ISNUMBER(FIND(" ",J11))=TRUE,ISNUMBER(FIND("_",J11))=TRUE),
IF(ISNUMBER(FIND("-",J11))=TRUE,SUBSTITUTE(J11,"-",""),IF(ISNUMBER(FIND(" ",J11))=TRUE,
SUBSTITUTE(J11," ",""),IF(ISNUMBER(FIND("_",J11))=TRUE,SUBSTITUTE(J11,"_",""),J11))),J11),""))
Because TRIM is error proof, we can call it one time on the result of the rest of the formula and simplify. If you find you’re using the same function over and over, ask yourself if you can’t move it more to the outside and call it less.
Multi-cell Formulas
I’m a big fan of huge one-cell formulas, but I shouldn’t be (an neither should you). If you’re typing the same sequence over and over in a formula, move that sub-formula out to a new cell and refer to the cell in your main formula. The classic case of this is the VLOOKUP function. In certain cases, VLOOKUP returns an error when it can’t find the value. To catch this, you call it twice, like this:
=IF(ISNA(VLOOKUP(A1,Sheet2!A1:C100,3,FALSE)),"Not found",VLOOKUP(A1,Sheet2!A1:C100,3,FALSE))
If you put the VLOOKUP in another cell, like B1, you could say
=IF(ISNA(B1),"Not Found",B1)
Share your formula tips in the comments.