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 Super Links #129 – shared by David Hager

 

Excel Magic Trick 1455: Convert Text Month Criteria to Upper & Lower Dates for SUMIFS Function

https://www.youtube.com/watch?v=q9MmmU0UBJU

Excel Find and Remove Duplicates

https://www.myonlinetraininghub.com/excel-find-and-remove-duplicates

Find last value in a column

https://www.get-digital-help.com/2017/08/16/find-last-value-in-a-column/

UnicodeBox ()

http://www.excelgaard.dk/Lib/UnicodeBox/

Extracting Ingredients from an #Excel List by Cost Category by David Hager

https://dhexcel1.wordpress.com/2017/08/15/extracting-ingredients-from-an-excel-list-by-cost-category-by-david-hager/

#Excel Super Links #128 – shared by David Hager

 

Unique distinct list sorted alphabetically and based on a condition

https://www.get-digital-help.com/2017/08/15/unique-distinct-list-sorted-alphabetically-and-based-on-a-condition/

What is DAX?

https://exceleratorbi.com.au/what-is-dax/

How to Easily Manage Your Inventory in Excel

https://www.datanumen.com/blogs/easily-manage-inventory-excel/

Feed the dragons in Game of Thrones with Excel 365 TextJoin

https://teylyn.com/2017/08/14/feed-the-dragons-in-game-of-thrones-with-excel-365-textjoin/comment-page-1/#comment-433

Using the #Excel Advanced Filter with a List Having Internal Numbers in a String By David Hager

https://dhexcel1.wordpress.com/2017/03/25/using-the-excel-advanced-filter-to-a-list-having-internal-numbers-in-a-string-by-david-hager/

 

Extracting Ingredients from an #Excel List by Cost Category by David Hager

Disclaimer: This technique uses the TEXTJOIN function. You need the Excel version included in Office 365 for the TEXTJOIN formula to work.

There are quite a few examples of a material based on ingredients. These include recipes, blends, paint mixtures, etc. Presented here is a method of extractiong from a list the ingredients for each mixture based on whether they have a low, medium or high cost.

The main list has 3 columns: Mixture, Ingredient and Cost. The lookup table has a list of ingredients based on cost category, as shown in the figure.

 IngredientListByCost1

The main formula from cell G2 is

=TEXTJOIN(“, “,TRUE,IF(((Mixture=$F2)+(Cost=G$1))=2,Ingredient,””))

This formula is filled to complete the lookup table.

If the mixture and the cost are correct for the formula based on its position in the table, it will return the corresponding ingredient into an array. The other array elements are left blank. Then, the TEXTJOIN function concatenates the elements of the array, excluding blanks.

I hpe that you find this useful!

You can download the file here.

IngredientListByCost

 

#Excel Super Links #127 – shared by David Hager

 

Best Practices for Sharing Power BI Reports

https://powerbi.tips/2017/08/best-practices-for-sharing/

xlRelease ()

http://www.excelgaard.dk/Lib/xlRelease/

AutoRecover Excel File

http://www.excel-easy.com/examples/autorecover.html

Build your own usage report with Power BI

https://www.kasperonbi.com/build-your-own-usage-report-with-power-bi/

Using #Excel VBA to Create a Filter Criteria User Defined Function by David Hager

https://dhexcel1.wordpress.com/2017/04/06/using-excel-vba-to-create-a-filter-criteria-function-by-david-hager/

 

#Excel Super Links #126 – shared by David Hager

 

Use VLOOKUP and return multiple values sorted from A to Z

https://www.get-digital-help.com/2017/08/11/use-vlookup-and-return-multiple-values-sorted-from-a-to-z/

Excel to Alglib via xlwings

https://newtonexcelbach.wordpress.com/2017/08/11/excel-to-alglib-via-xlwings/

How to Auto Adjust Combo Box or List based on Dynamic Data Ranges in Excel

https://www.datanumen.com/blogs/auto-adjust-combo-box-list-based-dynamic-data-ranges-excel/

The 7 most dangerous Excel features

https://exceloffthegrid.com/the-most-dangerous-excel-features/

Over 150 Articles with Valuable Excel Content

https://dhexcel1.wordpress.com/feed/

 

#Excel Super Links #125 – shared by David Hager

 

New M-function: Table.TransformColumnTypesToFirstRowsTypes for PowerBI and PowerQuery

http://www.thebiccountant.com/2017/08/10/table-transform-column-types-to-first-rows-types/

Employee Performance Panel Charts in Power BI with R

http://chandoo.org/wp/2017/08/11/power-bi-panel-charts/

Excel Magic Trick 1454 DAX Measure Count Customer Totals Between Upper Lower Limits, Each Month

https://www.youtube.com/watch?v=B3KiMQ4yFa4

You Can Now Put Values On Rows In Power BI

https://datasavvy.me/2017/08/10/you-can-now-put-values-on-rows-in-power-bi/amp/

My Collection of Excel Tips by David Hager

https://dhexcel1.wordpress.com/2017/03/11/my-collection-of-excel-tips-by-david-hager/