Operational Governance Knowledge Flashcards No. 09/19

Excel

From the Operational Governance flashcard deck; today this discipline goes by BizOps. The original card is republished here as a field note.

01 What is it

This is the knowledge that makes spreadsheets really worth using instead of your phone’s calculator: the best Excel formulas for business decision-makers.

02 When is it useful

  • When the quality of the numeric analysis will determine the quality of the business decision.
  • When you need to make a good decision, and fast.
  • When you need to convincingly communicate an idea.

03 How to use it

Get to love them, and they will be your best friends.

Your muscle reflex should default to these tools instead of something else.

04 The formulas, and what each is better than

Formula / ToolUse it when…Better than…
The dollar $ signBuilding a tool that is robust to maintain and scaleA dumbass not using $
=index(match(),match())Looking up data in a report tool that needs to be robust and versatilevlookup, hlookup
=sumifs(), =averageifs(), =countifs()Math operations on data that meets multiple conditional criteriasumif, countif
=concatenate()Concatenating text. Useful for certain use cases of index(match())Using the &
Pivot Table + Slicers + Calculated fieldsExploratory data analysis; quickly generating a report from multiple perspectivesMixing a million if formulas
=getpivotdata()Sourcing a pivot table output into a more visual reportPresenting the pivot table itself
Named RangesGetting your data house in order='Sheet Reference'!AL3:ZM8
Goal SeekSolving for a variable in an equation (despejando la X)Manual trial and error
Solver (add-in)Finding the optimal number for several variables simultaneously (optimizing a function)Manual trial and error nightmares
Data tableSeeing how an output varies as different inputs vary tooSeeing one output at a time

05 Common pitfalls

Not believing — i.e. thinking that it’s too complicated and therefore not worth doing.

06 References and resources