Monthly Archives: April 2014

The Excel Rosetta Stone – Decoding Complex Excel Functions and Formulas

Recently, I’ve been getting quite a few questions about ways to understand complicated Excel formulas.  To put it in simple terms breaking down an Excel formula is like learning another language.  At first glance it may seem difficult and daunting but once you have the “Rosetta Stone” it becomes simple and seamless.  The following outlines a few tools and techniques which can be used to unlock the mysteries of Excel functions and formulas.

 

Evaluate Formula Tool

Complex functions and formulas are typically nested within one another.  What is nesting? Nesting is the process of combining multiple functions into a long formula.  There are pros and cons to creating a large nested formula.  The Pro: Spreadsheet real estate is saved.  A complex calculation is included in one cell. The Con: A large formula is included in one cell.  Large formulas are usually harder to understand and take time to correct or debug.  The evaluate formula tool is one of those tools which can help unlock the mystery behind long complex formulas and functions.   This tool essentially walks you through each calculation step that Excel takes until a final result is displayed.

Select Formula Menue

Select Formula Menu

  1. Click on Formulas
  2. Click on Evaluate Formula

3. The formula in the cell appears in the evaluation window

4. Click Evaluate to watch the first calculation step

  5. Click Evaluate additional times to watch each calculation step

6. The last evaluation step shows a simplified version of the original formula

Function Button

The function button sometimes referred to as FX is a tool which is rarely used but has a tremendous benefit. The FX function is like a dictionary, it gives additional description about a function.  In addition when clicked a dialogue box appears which reveals step by step instructions on how to use the function.  It’s a huge time saver and it helps pinpoint common errors.   In order to use this function to begin to understand a complex function click on the cell that contains the function then on the Fx button.  A dialogue box is exposed showing the purpose of the function, the elements needed and the intended result.  Below are a few brief steps on how to use it.

  1. Click on the Fx button next to the formula bar

Note: The active cell must contain the formula

2. A dialogue box is revealed outlining the following

a. Description of required  elements

b. General description of function

c. Anticipated result based on populated values

 

Understanding the Symbols

 Another way to begin to easily understand complex functions is to understand the operators and symbols. Every Excel formula has symbols and each carries a specific meaning.  Very often using the wrong symbol can be the difference between accuracy and error.  For example there are mathematical operators which are needed for arithmetic in Excel.  In addition there are other characters which are used to define worksheet locations etc.  In our post titled Excel Characters, we highlighted different symbols used in Excel and there meaning.  It’s a great primer to understanding complex functions.

Complex formulas and functions will never be eliminated from the world of Excel.  With the tools and techniques mentioned above these formulas and functions can be translated into plain language.