Tag Archives: FORMULATEXT

Documenting Formulas in an #Excel Range Using the FORMULATEXT, ISFORMULA and TEXTJOIN Functions by David Hager

 

Documenting Excel formulas is an important step in the auditing of spreadsheets. Presented here is a new technique to aid in that process.

The utility of the recently added FORMULATEXT, ISFORMULA and TEXTJOIN functions is sometimes underestimated. The FORMULATEXT and ISFORMULA functions were introduced with Excel 2013 and the TEXTJOIN function is available only the Excel 2016 version which comes with an Office 365 subscription. In the technique demonstrated in this article, al three of these functions are used in a single formula. For the result, see the following figure:

 FormulaText_TEXTJOIN1

The key formula (in cell K1) is

=TEXTJOIN(CHAR(10),TRUE,IF(ISFORMULA(fRange),ADDRESS(ROW(fRange),COLUMN(fRange))&”:”&FORMULATEXT(fRange),””))

To understand how this formula works, lets break it down into its main parts.

ADDRESS(ROW(fRange),COLUMN(fRange)) returns an array of cell addresses for the desired range (in this case, fRange, which is E1:F13).

FORMULATEXT(fRange) returns an array of formulas for that range.

When these two formulas are concatenated with a colon, you get an array that looks like this.

{#N/A,#N/A;#N/A,#N/A;#N/A,#N/A;#N/A,#N/A;#N/A,#N/A;#N/A,#N/A;#N/A,#N/A;#N/A,#N/A;#N/A,#N/A;#N/A,#N/A;#N/A,#N/A;#N/A,#N/A;”$E$13:=SUM(E1:E12)”,”$F$13:=SUM(F1:F12)”}

In this example, formulas only exist in cells E13 and F13, with the rest of the array elements as #N/A. When the ISFORMULA function is applied to the range is question, the result is

{FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,TRUE}

So, the final array created by the IF function looks like this.

{“”,””;””,””;””,””;””,””;””,””;””,””;””,””;””,””;””,””;””,””;””,””;””,””;”$E$13:=SUM(E1:E12)”,”$F$13:=SUM(F1:F12)”}

When this array is processed by the TEXTJOIN function, it affords a string of formulas with their corresponding cell locations.

Note that this technique can also be used with non-contiguous ranges. Since the TEXTJOIN function can use additional arrays as arguments (see https://dhexcel1.wordpress.com/2017/05/21/excel-short-and-sweet-tip-15-using-textjoin-with-non-contiguous-ranges-by-david-hager/ ), the same array formula used in the 3rd argument of this TEXTJOIN example can be used in subsequent arguments, provided that the desired ranges for those arrays are different.

You can download the example file here.

FormulaText_TEXTJOIN

 

#Excel: Using Conditional Formatting to Highlight Cells That Contain Array Formulas Using the FORMULATEXT Function by David Hager

A common story among Excel developers is the easy fixes they made for their clients to get a formula in a cell to calculate correctly. In many cases, the problem was corrected by simply entering a formula as an array formula (Control-Shift-Enter). This only takes seconds to perform, and usually the developer received a hefty amount of money relative to the task. So, if a conditional format can be applied to worksheet cells that potentially contain array formulas, the CF highlight will indicate which are array formulas.

Originally, I thought of creating an UDF with VBA that could be used as the CF Boolean formula. Then, I remembered the new Excel FORMULATEXT function and wondered whether it would return the “curly brackets” from a cell containing an array formula. I doubted that it would, but it did! 😊 So, I made a defined named formula (with E6 as the active cell) as shown in the figure below.

IsArray2

The formula, IsArrayFormula=LEFT(FORMULATEXT(E6),1)=”{“, is True if the first character in the string is a left curly bracket. Then, I was able to use that formula as a conditional format, as shown below.

IsArray1

Now, if this technique is used in auditing Excel workbooks, the cells containing array formulas will be able to be easily viewed.

The example workbook can be downloaded here.

IsArray