A Foolproof Approach To Writing Complex Excel Formulas —Search Engine Land

Posted date: February 05, 2013 In: Practice Management, QuickPress

Annie Cushing is on a Mission to Rid the World of Ugly Data. Learn About Data Collection, Analytics, Technical SEO, Analysis, and Beautification.

Annie Cushing, a Senior SEO at SEER Interactive, a Philadelphia-based online marketing agency, explains that “There comes a time in a marketer’s life when making pretty charts with a predefined dataset just doesn’t cut it.

“And finding the sum and average of a column of data just doesn’t satisfy you anymore. Eventually — and it’s really inevitable — you will actually have to dive deep into the data and cull out a smaller dataset or manipulate it in some way to make it cough up what you need. And, as freaking intimidating as they can be, formulas become your lifeline in these moments.”

Admittedly, she explains, the learning curve with formulas can be pretty steep, but the only way they’ll become intuitive is exposure to them and practice. So here’s some insight:

Excel Formulas Still Scare Me Yet I Did This (You Can Too!)

However, when I was first learning them, I’d see a formula like the one below, which I wrote to find the last directory in a URL (just to see if I could, not for any particular use), and think that I’d never ever be able to do anything like that. Turns out I was dead wrong.

. . . Before you throw in the towel, let me tell you a trick I first saw Bill Jelen (AKA Mr. Excel) do that makes writing formulas — even advanced formulas like this one — much simpler. . . . So, enough pontificating. Let me show it to you in action. But first let’s cover some terminology. And don’t worry; we’ll start with a much simpler task than that data voodoo.

Definitions

Formula: Formulas provide some kind of instructions for Excel to calculate something. They always start with an equal sign. It can be as simple as =2+2 or ridiculously complex like the one above.

Function: In the simplest terms, functions are formulas that come prepackaged in Excel. I mean, you could create your own functions, but most of the time you’ll be using one of the 80 bajillion offered natively in Excel (unless, of course, you want percent difference, which Excel doesn’t offer … go figure). With the more advanced formulas you’ll write, you will use multiple functions in one formula.

Argument: Each function starts with the function name, followed by a set of parentheses, e.g., =SUM(A3:A67) or =CONCATENATE(B2,C2,D2). The individual elements inside those surrounding parentheses, separated by commas, are arguments.

Square brackets: Sometimes you’ll see arguments listed in square brackets in the tool tip or on the Microsoft site. The brackets indicate that the argument is optional.

Boolean: Returns a TRUE or FALSE value.

Helper cells: Okay, this isn’t an actual term; it’s what I call the cells you use to break down a complicated formula into easy-to-understand steps.

Ms. Cushing goes on to explain how to download Excel data sheets that can be already pre-set with the formulas and datas you need, you to extract domain information from URLs, additional strategies for formula manipulation, ways of comparing two datasets, and much, much more.

It’s said that some 90% of valuators don’t use automated software (such as ValuSource) that could make their lives easier, particularly if they do many business valuations, and if those valuations are complex. Which is to say: A huge majority of you use Excel.

These tips are invaluable.

Value it Accurately. Learn to Master Excel.

Related Entries

National Association of Certified Valuators and Analysts

The National Association of Certified Valuators and Analysts (NACVA) supports the users of business and intangible asset valuation services and financial forensic services, including damages determinations of all kinds and fraud detection and prevention, by training and certifying financial professionals in these disciplines.

Number of Entries : 2603