Chapter on Conditional Formatting in Excel by David Hager (a Blast from the Past)

I wrote this conditional formatting article almost 20 years ago, with the hope that it would  be included in a book. Unfortunately, it never made it. I then published it as part of my Excel E-Experts series. The text files were recently added to this blog.

https://dhexcel1.wordpress.com/2017/01/07/archive-of-excel-experts-e-letter-by-david-hager/

This article was perhaps one of my best works in Excel. So, although it was written long ago, it still contains valuable information that I am sharing here. The following file links are the working Excel file and the article document. Enjoy!

CONDFORMAT1

CFarticle

 

 

My Collection of Excel Tips by David Hager

“#Excel, did you know?” that you can use a CUBE function in an #Excel conditional formatting formula.
“#Excel 2013 did you know” that there are download files in Help for new worksheet functions.
“#Excel, did you know?” that chart based on TABLE will auto-expand when new data is added.
“#Excel, did you know” that you drag/drop data from one #Excel window to another?
“#Excel did you know?” you can use a defined name range on an xl4 macro sheet in a formula?
“#Excel did you know?” that you can compare two columns by using GoTo Special – Row Differences?
“#Excel did you know?” that if a number of worksheets are selected, right-clicking one will make it the active sheet.
“#Excel, did you know?” magic UDF part 2: .AddComment .Comment.Text CStr(Now()) End With End Function
“#Excel, did you know?” magic UDF part 1: Function TS() Application.Volatile True On Error Resume Next TS = “TS” With Application.Caller
“#Excel, did you know?” you can make and store your own custom cell styles.
“#Excel, did you know?” if you fill a table across worksheets, new tables are made automatically in the destination worksheets.
“#Excel, did you know?” you can import XML from Internet which becomes a data table that you can then load into PowerPivot.
“#Excel, did you know?” that you can use Insert, Object to place a linked Excel file inside itself.
“#Excel, did you know?” there are >250 commands that are not accessible through the ribbon, but can be added to the Quick Access Toolbar.
“#Excel, did you know?” you can r-cl the cell below a text list & select “Pick from drop-down list” to pick an entry in the list.
“#Excel, did you know?” you can right-click the scroll buttons in lower left corner to access a list of all worksheets in workbook.
“#Excel, did you know?” you can create a defined name formula (DNF) from an UDF without arguments.The DNF can then be used in other formulas.
“#Excel, did you know?” you can write an external link formula in a cell that points to a cell in an #Excel file stored on the Internet.
“#Excel, did you know?” you can return the sum of top 3 values of a filtered column with =SUM(AGGREGATE(14,1,filtered_col,ROW(1:3))) CSE
“#Excel, did you know?” r-cl a shape, cl 1st option & enter VBA procname in Address box preceded by #. cl on shape to go to that proc in VBE.
“#Excel, did you know?” you can link a data range in an external workbook to a sparkline.
“#Excel, did you know?” you can link a cell in an external workbook to a shape.
“#Excel, did you know?” Convert formulas to values: Select range, right-click edge, pull away, drop in original area and select menu item.
“#Excel, did you know?” that sparklines can use defined name formulas such as =OFFSET($A$1,1,$G$1,COUNTA($A:$A)-1,1) as their input range.
“#Excel, did you know?” that sparklines autoadjust with auto-expansion of tables if associated with a vertical range.
“#Excel, did you know?” that sparklines respond to filters in tables if associated with a vertical range.

Using the CHOOSE and AGGREGATE Functions To Apply Conditional Formatting to a Filtered List in Excel By David Hager

The very 1st article I wrote for my blog was about highlighting values in a filtered list. I decided that it was time to revisit that subject. Specifically, I had written an article for Rob Collie about using the CHOOSE function with conditional formatting (CF) in Excel. It turned out that the article was too complex to be useful.

https://dhexcel1.wordpress.com/2013/12/19/highlighting-top-n-values-in-a-filtered-table-column-using-conditional-formatting/

https://powerpivotpro.com/2012/11/david-hager-on-dynamic-conditional-formatting/

So, I decided to incorporate the use of the CHOOSE function into my original article and give it a friendly user interface. The two user inputs were both created using Data Validation with the List option. They are defined as shown below and pictured in the following figure (showing unfiltered list).

critunfilt

TopN=Sheet1!$B$1

Crit=Sheet1!$B$2

These inputs are used to return values needed by the CF conditions. TopN is selected by the user in cell B1. Cell B2 gives a choice of Large or Small. The following formula converts this information into a number to be used by the CHOOSE function.

CritNumber=IF(Crit=”Large”,1,2)

The CF conditions are made to be applied to column B. When each formula is defined, the active cell needs to be B6.

LargeCondition=OR(Sheet1!B6>=AGGREGATE(14,1,Sheet1!$B$6:$B$31,ROW(INDIRECT(“$1:$”&TopN))))

critlarge

SmallCondition=OR(Sheet1!B6<=AGGREGATE(15,1,Sheet1!$B$6:$B$31,ROW(INDIRECT(“$1:$”&TopN))))

critsmall

When combined with the CHOOSE function (the formula used for the CF), they provide a methodology to highlight filtered rows, as shown below (filter set to display values of 50 to 175 in column B).

=CHOOSE(CritNumber,LargeCondition,SmallCondition)

If a way to visualize TopN and BottomN simultaneously was required, a second CF condition can be appled to column B.

=CHOOSE(CritNumber, SmallCondition, LargeCondition)

The following figure shows the result.

critboth

This is just a demonstration of the versatility of using CF condtions in filtered lists. The application of this method to many columns can be done, as well as the use of different CF conditions. I might write another article about this subject later, but for now, enjoy! Click the link below to download the working file.

aggregate_filter_choose

Using the Excel TEXTJOIN function to Extract an Internal Numeric String From a String by David Hager

Before I had access to Excel 2016 (365), I made a comment on Chris Webb’s web site about a possible use for the new TEXTJOIN function.

https://blog.crossjoin.co.uk/2016/02/05/new-ways-to-concatenate-text-in-excel-2016-with-concat-and-textjoin/

Here is the comment:

… the following array formula will return a numeric string from a string (i.e. – 125 from AMDHF125KOI) by using this formula. =TEXTJOIN(“”,TRUE,IFERROR(VALUE(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)),””))

Chris confirmed that it worked!

Afterwards, I forgot about it, until I read the article on XOR LX’s web site about extracting numbers from a string.

https://excelxor.com/category/extracting-numbers-from-a-string/

In looking at the comparisons, I saw that this TEXTJOIN formula construction simplified the extraction of an internal numeric string from a string as compared to previous solutions.

I am looking at ways to extend this methodology to other string-to array-to string solutions.

Averaging of Scientific Results in Excel

Each scientific measurement process has its own criteria for what is acceptable data. However, there are some general rules. If a data point is suspect, another is generated. If these are within the statistical variation for that process, then they are averaged. However, if a 3rd measurement is needed,

An Excel formula that correctly handles all of these requirements is needed. If 2 of the measurements are within statistical variation, they are averaged. This would be the two closest values.

But if two measurements are equidistant from the 3rd, (13.2,13.5,13.8) then all 3 values

must be averaged.

So, given those requirements, the following formula will return the correct result.

 

=IF(SUM(MATCH($A$1:$C$1,$A$1:$C$1,0))<6,QUARTILE($A$1:$C$1,1+2*(MEDIAN($A$1:$C$1)>SUM($A$1:$C$1)/3)),AVERAGE(IF(LARGE(ROUND(ABS($A$1:$C$1-TRANSPOSE($A$1:$C$1)),3),5)=ROUND(ABS($A$1:$C$1-TRANSPOSE($A$1:$C$1)),3),$A$1:$C$1,””)))

 

First, if the 3 values are all different, then

=SUM(MATCH($A$1:$C$1,$A$1:$C$1,0))=6 is True.

So, if SUM(MATCH($A$1:$C$1,$A$1:$C$1,0))<6,then the formula uses

=QUARTILE($A$1:$C$1,1+2*(MEDIAN($A$1:$C$1)>SUM($A$1:$C$1)/3))

In the comments at this site:

http://chandoo.org/wp/2011/01/19/average-of-closest-2-numbers/

a poster in the comments named Ihm came up with this formula.

In the FALSE condition of the formula is the following:

=AVERAGE(IF(LARGE(ROUND(ABS($A$1:$C$1-TRANSPOSE($A$1:$C$1)),3),5)=ROUND(ABS($A$1:$C$1-TRANSPOSE($A$1:$C$1)),3),$A$1:$C$1,””))

It is based on a 3×3 matrix obtained from

=ROUND(ABS($A$1:$C$1-TRANSPOSE($A$1:$C$1)),3)

For a data set of

13.4 13.55 13.8

The following matrix is

{0,0.25,0.4;0.25,0,0.15;0.4,0.15,0}

The ROUND function is necessary since the ABS function sometimes introduces differences (i.e. – 0.25 vs 0.2499999999).

The 5th largest value in the matrix corresponds to the smallest difference. The values for those differences in A1:C1 are averaged to afford the desired result.

 

Charting Data With Formula Links In Excel By David Hager

You might have a need to chart data that is formula linked from its original source. For example, the data is generated in Columns A (Date) & B (Data), but you want to chart the data from Columns U & V. So, you would put this formula in cell U1 (=A1) copy it to Column V and fill down to whatever # of rows you needed. As the data is generated in A & B, you would then want to chart the data from U & V. In the following Figure is an example what U & V might look like.

dhpicture1

Notice what the columns look like where no information has been added at the data source. The column containing the dates return at the bottom 01/00/1900, which is 0 with a date format. The column containing the data return at the bottom 0’s. An attempt to chart this data as is shown in the following Figure. = SERIES(Control!$V$1,Control!$U$2:$U$39,Control!$V$2:$V$39,1)

dhpicture2

Clearly, the chart does not produce the desired effect. A method of charting just the “good” data is needed. In order to identify the end of the good data, this defined name formula looks at column V and finds the 1st position of a 0.

EndOfData= MATCH(0,Control!$V:$V,0)-2

Actually, the formula for doing that would exclude the -2, but it is needed for sizing the subsequent OFFSET formulas.

DateRange= OFFSET(Control!$U:$U,1,,EndOfData,)

Data Range= OFFSET(Control!$V:$V,1,,EndOfData,)

So, the defined names formulas can now be used in the chart series formula.

=SERIES(Control!$V$1,ChartDraw.xlsm!DateRange,ChartDraw.xlsm!DataRange,1)

Note that the defined names are at the workbook level. The desired chart is shown below.

dhpicture3

Hope this helps!