Practical Applications

This section provides some examples of the use of selected Information functions in data analysis and manipulation, including assessing data integrity, and providing other information.

Example: In-formula Comments Using ISTEXT, ISNUMBER or N

The ISTEXT function allows one to note or document a model by using in-formulae comments. This represents an alternative to using comments attached to cells, having the advantage that the comments can be seen when Excel is switched to its formula view (Formulas/Show Formulas). Thus, when auditing a model by inspecting its formulae, the comments are directly available.

The file Ch22.1.InCellComments.xlsx shows an example of the two approaches (when the model is displayed in formula view) (see Figure 22.1). In Cell C6, the ISTEXT function is used to provide a comment; since this function evaluates as TRUE (treated as 1 by Excel), the numerical result of the calculation is equal to the original value (12000). In Cell D6, the attached comment approach is shown, i.e. in which the comment has to be inspected separately to see its contents.

Note that the functions ISNUMBER or N could also be used in this context: for example, ISNUMBER(“The comment”) or N(“The comment…”) would be added to the core cell formula (rather than multiplied), as the presence of the text comment would result in each function evaluating to zero.

Example: Building a Forecast Model that Can Be Updated with Actual Reported Figures

The ISBLANK function can be used to determine whether a cell is blank (sometimes, ISBLANK embedded within a NOT function is required to determine that a cell is not blank). One application is when models are built to be updated with actuals as they become available, but to use forecast figures until the actuals are provided.

The file Ch22.2.UpdateActuals.xlsx shows an example (see Figure 22.2):

• The first part of the file (cells B2:I4) show a traditional forecasting model in which future sales are calculated based on assumptions about the value in the starting year (2016) and the growth rates in each future year. The first approach is suitable for one-off use (such as for a corporate finance transaction). However, significant rework would be needed if the model were required to be updated as data for future years become available (such as 2017 data at the end of that year). Of course, for a two-line model, this rework could be done very quickly, but for larger models containing many line items, the process would be cumbersome and error-prone.
• The second part of the file shows how the model may be adapted, so that the assumptions used to create the forecast depend on whether actual reported data is present, detected using the ISBLANK function. Where no reported data is present, the original forecast assumptions are used, otherwise assumptions are reset so that the resulting forecast replicates the reported figures.

Note that the =NOT(ISBLANK(G3)) combination will return a 1 in all cases where cell G3 has any content, whereas (as mentioned in Chapter 17 and Chapter 9), the use of =IF(G3,1,0) would return a 1 if cell G3 contains a non-zero number, a zero if G3 is zero and #VALUE in the case of a text entry. It is also worth noting that in such contexts, the difference between a blank cell and one containing zero in the actual field is fundamental; a blank cell means that no actual figure has yet been reported, whereas a cell containing zero means that the actual figure has been reported and that its value is zero (which is of course a valid value in many contexts, such as for the number of serious accidents that may have happened in the month).

Example: Detecting Consistency of Data in a Database

The ISBLANK functions can be used in some cases to detect inconsistency in a data set.

The file Ch22.3.ISBLANK.Consistency.xlsx (see Figure 22.3) shows an example of a set of contracts, each of which should either have both a start and end date (for existing contracts) or have both date fields as blank (for contracts currently being negotiated, for example). The ISBLANK function is used within an IF statement to detect inconsistent values, which evaluate to zero (Conditional Formatting is also used to highlight these values).

Of course, it is worth noting that other functions (such as ISNUMBER or ISTEXT) may be required in similar situations.

Example: Consistent use of “N/A” in Models

The text expression “NA” is often used within IF statements, for example to indicate that an inconsistency has arisen within the data. In general, the use of such text fields is not to be favoured (within a set of interim calculations), for subsequent calculations are likely to be more error-prone. For example, the need to begin many dependent formulae with items such as IF(G56=“NA”… . .) will add complexity, reduce transparency and likely lead to errors (e.g. due to a misspelling or incorrect interpretation of the text field “NA”, such as if it is written “#N/A”).

Alternatives include:

• Using a numerical indicator (such as zero) to indicate inconsistency. This is the generally the author's preferred approach, as it retains the basic integrity of (most) models (as a set of numerical calculations), and tends to keep subsequent formulae simpler.
• Using the “=NA()” function to consistently treat such items in Excel.

The file Ch22.4.NA.Consistency.xlsx shows an example of this latter approach, comparing the use of the “NA” text field with the “=NA()” function. The latter is generally more consistent than the former in terms of delivering consistent #N/A error values when the formulae referring to the data are inapplicable. Figure 22.4 shows a screen-clip of the example.

Example: Applications of the INFO and CELL Functions: An Overview

The INFO and CELL functions can be useful both to document models and to find out information about the current operating environment. This can be especially important in more-advanced applications, including the writing of robust VBA code.

The INFO function generally provides functionality that relate to a workbook or operating environment:

• The path of the current directory or folder. This is often important to know when writing VBA code that needs to work with all files in the same directory as the main model.
• The number of active worksheets in open workbooks.
• The cell reference of the top and left-most cell visible (as text).
• The current operating system version (as text).
• The current recalculation mode: Automatic or Manual.
• The version of Microsoft Excel being used (as text). This can be useful when writing VBA code to ensure that the latest version of Excel functionality is being used, such as in the creation of “wrapper” functions; see Chapter 33 for an example.
• The name of the operating environment: “mac” or “pcdos”.

The CELL function provides information about the position, content or format of a cell or a referenced range, including:

• The full path of the file that contains the reference range (as text).
• The address (as text) of the first cell in the referenced range, or the address of the most recently changed cell in the workbook if the referenced range is omitted.
• The column number of the cell that is referenced.
• The row number of the cell that is referenced.
• Whether a cell is formatted in colour for negative values or not.
• The contents of the upper-left cell in the range that is referenced.
• The number format of the cell.
• Whether a cell is formatted with parentheses for positive or for all values.
• The label prefix of the cell, i.e. returns single quotation mark (') if the cell contains left-aligned text, double quotation mark (") if the cell contains right-aligned text etc.
• Whether a cell is locked (protected) or not.
• The type of data in a cell (e.g. returns “b” for blank if the cell is empty, “l” for label if the cell contains a text constant and “v” for value if the cell contains anything else).
• The column width of the cell (rounded to an integer), where each unit of column width is equal to the width of one character in the default font size.

The file Ch22.5.INFO&CELL.Overview.xlsx contains examples of each of these (see Figure 22.5).

Example: Creating Updating Labels that Refer to Data or Formulae

It can sometimes be useful to create labels that reference cells or ranges. For example, if a data set starts at Cell C5, one may wish to create a label such as “Data starts in C5”, with the label referring to the cell (so that it would update if a new row or column were introduced). Such a label can be created with a formula such as:

The file Ch22.6.FormulaLabels.xlsx contains an example (see Figure 22.6).

Example: Showing the User Which Recalculation Mode the File Is On

It may be important in some cases to clearly and explicitly let a user know what recalculation mode Excel is set to. For example, some models may need to be set to Manual calculation as they are large (or slow to recalculate for other reasons), so that recalculation may be conducted only when necessary, rather than automatically after every change is made. Of course, setting a workbook to Manual recalculation is potentially error-prone if someone were to change the input data and use the results whilst overlooking the need to recalculate the workbook. Therefore, one may wish both to explicitly show the recalculation mode as well as to highlight when it is set to Manual by using Excel's Conditional Formatting.

The file Ch22.7.RecalcInfo.xlsx contains an example in which the “Recalc” form of the INFO function is used (in practice, this would be placed somewhere that is clear to the user or within the description of the model within the Excel file itself). Conditional Formatting is used to shade the cell (yellow in the electronic file) when it shows the value of “Manual” (see Figure 22.7).

Example: Finding the Excel Version Used and Creating Backward Compatible Formulae

The use of INFO(“RELEASE”) will provide information about the Excel version being used; Excel 2003 is release 11, Excel 2007 release 12, Excel 2010 release 14, Excel 2013 release 15 and Excel 2016 is release 16. One example of the use of this knowledge is to create formulae that use the latest form of the functions, as available in the user's Excel version. For example, one may wish in principle for the model to use the NORM.S.INV function (see Chapter 21), but the NORMSINV function would be used for users with version 11 or earlier.

The file Ch22.8.ReleaseCompatability.xlsx contains an example (see Figure 22.8). Note that the release information resulting from using INFO(“RELEASE”) is a text field, but which for most purposes could be used without consequence as if it were a number. However, it is generally more robust to apply the VALUE function (see Chapter 24) to turn this apparently numerical text field into its corresponding number. The formula in Cell C6 then uses one or other of the inversion functions, depending on the release version, in order to calculate the inverse (percentile) of the standard normal distribution for the percentage value generated by the RAND() function.

As discussed in Chapter 33, such functionality could be built into a VBA user-defined “wrapper” function, which would be easier to maintain (as new Excel versions become available), especially if used in several places.

Example: File Location and Structural Information Using CELL, INFO, SHEET and SHEETS

As mentioned above, the CELL and INFO functions may be used to provide the filename and directory of the current file. Other useful information can be the number of worksheets in a workbook, as well as (especially when writing VBA) the sheet number of a worksheet.

The file Ch22.9.FileDir&Sheets.xlsx shows an example of the use of the CELL and INFO functions, as well as of the SHEETS and SHEET functions (see Figure 22.9). Note that the order of the worksheets is Sheet1, Sheet2, Sheet4, Sheet3, with Sheet2 hidden. The SHEETS function shows that there are four sheets (i.e. including the hidden sheet) and the SHEET function shows that Sheet4 is the third sheet of the workbook (i.e. it also includes the hidden sheet). The SHEETS function also counts chart sheets (not just worksheets); thus, if one enters some data into the example file and presses the F11 short-cut for the insertion of a chart sheet, one will see the number of sheets increase.