**Formula Analyzer**

Use the Formula Analyzer to quickly understand complex formulas and easily navigate through the parts of a complex formula. The Formula Analyzer is also the easiest ways to jump to target ranges of formulas or formula parts.

Targets of complex formulas like INDEX, MATCH, VLOOKUP, OFFSET or INDIRECT can easily be determined using the Formula Analyzer. You can jump to the result of a complex formula with just a single click.

Best practice

**Formula Analyzer – basic functionality**

- If you select a cell that contains a formula the Formula Analyzer can be used to analyzse the parts of the formula
- Once you hit the
button the formula will be loaded into the Formula Analyzer and can be expanded part by part**Refresh** - If you want the formulas to be automatically be loaded into the Formula Analyzer when the selected cell changes enable the
toggle at the top right. Please note this may slow down navigation in the workbook.*Auto* - If a cell has previously been loaded into the Formula Analyzer the formula is cached. This is shown by the clock icon next to the formula. If you want to reload the formula hit the
button. Cached formulas are automatically reloaded if the underlying formula has changed since the last selection. However if a cell is selected and the formula is changed the**Refresh**button needs to be hit, to refresh the formula.*Refresh* - If you select any formula part in the expanded view the corresponding part in the formula will be shown in red letters in the formula at the top. In the example above the first parameter of the SUMIFS function has been selected. Therefore the range
*C1:C8*is highlighted in red. - If available, the result of each formula part will be calculated and shown in the second column. Results can have different types:
**Values:**If a formula part results in a value the value will be shown (eg 6 in the first row in the example above)**Single cell:**If a formula part results in a single cell reference the value of the cell will be shown (eg A in cell E1 in the example above). In addition a blue arrow will be shown in the third column.**Multiple cells:**If a formula part results in a cell range consisting of multiple cells a text will be shown stating how many cells are included in the range. The cell range can further be expanded. In addition a blue arrow will be shown next to the range. Cell ranges can be the result of defined ranges in formulas like A1:A8 in the example above, but also be the result of functions that return references like OFFSET or INDEX.

- Every time a
is shown next to a formula part it means that you can jump to the result of the formula part by clicking this button in the row*blue arrow*- Alternatively you can perform a
on the row, use the menu item in the context menu (right click on row) or use the button*double click*at the top*Jump to* - If the target worksheet is not visible (e.g. in hidden or very hidden mode) a message box will appear asking if the hidden worksheet should be set to visible. Please note: If a worksheet is set to visible during a jump to operation the worksheet will stay visible after the operation has finished.
- The
button enables you to quickly jump back to the previously selected cell if you jumped to another cell using the Jump to functionality*Jump back* - The
button also offers a drop down menu showing the latest cells which can directly be selected from there*Jump back*

- Alternatively you can perform a

**Settings of Formula Analyzer**

Settings can be accessed by clicking the drop down of the ** Refresh** button.

**The following settings are available**

**Auto refresh:**If this option is enabled the Formula Analyzer will automatically update whenever the selection changes.**Refresh:**This button does the same as thebutton at the top, it reloads the current cell to the Formula Analyzer*Refresh***Clear:**This button clears the Formula AnalyzerPlease not, this may slow down selection changes in very large workbooks**Expand node/collapse node:**This options expand/collapse the currently selected nodes**Limit ranges:**This option states how many rows are shown when expanding ranges before the*More cells*information is shown. The higher the number the more cells are shown when expanding ranges. Increasing this number may lead to slower operations when expanding ranges**Color target:**This option determines if the target of a formula (eg the SUMIFS parts in the example above) are colored in red**Evaluate formulas:**This option determines if formula parts are evaluated. Usually this option should be enabled. However you can disable it if evaluating complex formulas take too long.**Evaluate external formulas:**This option determines if formulas in external workbooks are evaluated. Usually this option should be turned off as evaluating external formulas may be very slow, especially if the external workbook is not opened**Cache cells:**This option determines how many cells are cached in history. Cached cells show a small clock icon next to the formula. Cached cells load faster than uncached cells and keep their expansion levels. If you want to reload a cached cell you can always press thebutton. Please not that a higher number of cached cells may slow down operations in the Formula Analyzer.*Refresh***Clear cache:**This button clears all cached cells**Rounding:**See explanations below

**Rounding Settings**

The smartRush Formula Analyzer offers comprehensive rounding capabilities. If rounding is active numbers within the Formula Analyzer will be rounded as configured. The following options are available:

**Round numbers:**If this option is enabled numbers within the Formula Analyzer will be rounded.**Number of decimals:**This selection determines the number of decimals in the rounded numbers. If eg**2**is selected here**12.1234**would be displayed as**12.12**, if**4**is selected**12.123**would be displayed- Percentage decimals: This selection determines the number of decimals for percentages in the rounded numbers. If eg 2 is selected the percentage
**12.1234%**would be shown as**12.12%**. **Use decimals of cell:**If this option is enabled the number of decimals from the resulting value in the cell will be applied, if applicable. If the cell does not contain a valid number the number of decimals selected in the dropdown menu will be applied. Please note the cell decimals are only applied to percentages if the cell contains a percentage. If the cell does not contain a percentage value the decimals will not be applied to percentages.**Always show decimals:**If this option is selected all decimal values will always be show. If two decimals have been selected and this option is enabled**12.1**would be shown as**12.10****Decimals for integers:**If this option is enabled integer numbers will show decimals as well. If two decimals are selected and this option is enabled**12**will be shown as**12.00**. If this option is not enabled**12**will be shown as**12**.**Round to decimals:**If this option is enabled the number will be rounded to the number of decimals selected. If this option is not enabled the number will be truncated. If enabled**1.55**will be rounded to**1.6**, if not enabled**1.55**will be shown as**1.5**.**Show leading 0:**If this option is enabled numbers greater than -1 and smaller than 1 will show a leading zero. The number**0.1234**with two decimals will be shown as**0.12**. If this option is not enabled the number will be shown as**.12**.**Treat -1 to 1 as %:**If this option is enabled all evaluated numbers between**-1**and**1**will be treated as percentages. In this case a value evaluated to**0.02**will be shown as**2%**.**Prefer cell format:**If this option is selected whenever a cell format is available for a formula part this format will be preferred over the settings.

**Advanced functionality**

The Formula Analyzer offers additional functionality for a variety of ExcelÂ® functions. Please note not all functionality may be available for every function. In comprehensive tests the Formula Analyzer was able to resolve 99.84% of all formulas.

**INDEX:**Cells that are part of the result are shown in red**MATCH:**The cell that matches in the lookup range is shown in red**VLOOKUP:**The cell that matches in the lookup range is shown in red**HLOOKUP:**The cell that matches in the lookup range is shown in red**LOOKUP:**The cell that matches in the lookup range is shown in red**IF:**The part of the IF that is active is shown in red**SUMIF, SUMIFS:**The cells in the sum-range that match all search criteria are shown in red. In addition within every criteria-range all cells that match the specific criteria are shown in red.**AVERAGEIF, AVERAGEIFS:**The cells in the average-range that match all search criteria are shown in red. In addition within every criteria-range all cells that match the specific criteria are shown in red.**CHOOSE:**The active part is shown in red**IFS:**The active part is shown in red- More function will follow in future updates

**Example of advanced functionality based on a SUMIFS formula**

For demonstration purposes the following simple SUMIFS formula with the two criteria A and C is used in this example. The sum of all matches is calculated from column C.

The following picture shows the matches for all criteria in red. In this example the cells **C1** with the value of **1** and the cell **C5** with the value of **5** match, resulting in a sum of **6**. These two cells match because they are the only cells matching both criteria **A** in column **A** and C in column **B**.

For further investigation each criteria can be analysed seperately. The following picture shows an excerpt of the first criteria matching to the value **A** in column A. All matches are shown in red.

**Example of advanced functionality – INDEX**

The following picture shows another common example consisting of a simple INDEX formula with two MATCH formulas. The picture shows that the overall formula evaluates to the value **7** in cell **D3**. Please note the ** blue arrow** next to the

**7**. Because the INDEX formula returns a single cell you can easily jump to the result of the INDEX formula by using the

**button.**

*blue arrow*In the example below the first MATCH part is selected in the formula analyzer. This is also shown by highlighting the corresponding part in the formula bar at the top. The red letters of **C1** indicate that this MATCH, as a part of the INDEX/MATCH combination, results in the value **B** pulled from cell **C1**.