This time you really hit the jackpot! >850 Excel and Power BI Links, all of the best articles. Make this the cornerstone of your Excel resource library. Download the Word document here.

Leave a reply

This time you really hit the jackpot! >850 Excel and Power BI Links, all of the best articles. Make this the cornerstone of your Excel resource library. Download the Word document here.

Advertisements

For this technique, I am building it on the workbook made for the following article. Please download the example file and read the article, since the functionality is synergistic with it.

When viewing a control chart, it is useful to be able to view only data within set control limits. For example, you would like to view all data within one sigma of the mean. The technique described here allows you to do that for 1, 2, or 3 sigma. The key to accomplish this is Excel’s advanced data filter. The advanced filter uses a boolean formula to filter a table of data, starting at the first row of the table. The formula in A2 gives the desired result.

=AND(B6<CHOOSE(SigmaKeep,D6,E6,F6),B6>CHOOSE(SigmaKeep,G6,H6,I6))

where SigmaKeep is a worksheet cell (G2) with a data validation list of 1,2,3. Cells D6 and G6 (and the corresponding columns) contain formulas that calculate the +1 and -1 sigma from the mean for the data in column B. The 2^{nd} and 3^{rd} sigma are for E6,H6 and F6,I6 respectively. The following figure shows the advanced filter dialog box and the input ranges required, where SigmaKeep is set at a value of 2.

After the filter is applied, note the difference in the data in the control chart versus the original in the first figure.

The only caveat to this technique is that the advanced filter has to be cleared (Data, Sort & Filter, Clear) before a different KeepSigma value can be applied. This can be used in conjunction with the removal of outliers as discussed in the original article.

The example file can be downloaded here.

I am extending the closest value technique I published recently to calculate the same based on a filtered list.

In this demonstration, the goal is to highlight values in a numeric range that are clostest to the average of that range in a filtered list. So, we first make the range dynamic with the following defined name formula.

NumRange =OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A:$A)-1)

Then, we modify that range to include only filtered values.

fNumRange =IFERROR(IF(SUBTOTAL(3,OFFSET(NumRange,ROW(NumRange)-MIN(ROW(NumRange)),,1)),NumRange,””),””)

For more information on the SUBTOTAL function as used here, see:

Next, we use that range to make an array of the absolute differences of each value of the range from the average.

ABS_Range =IFERROR(ABS(fNumRange-AVERAGE(fNumRange)),””)

We can then define a cell for the number of values to highlight.

N_Values =$B$2

The heavy work is done by the next formula, which creates an array of the N values to be higlighted.

Num_Array=INDEX(NumRange,N(IF(1,TRANSPOSE(MATCH(SMALL(ABS_Range,ROW(

INDIRECT(“1:”&N_Values))),ABS_Range,0)))))

This formula returns the position of each smallest deviation in the 2^{nd} argument of the INDEX function, which then returns the values corresponding to those deviations, based on a filtered list. The use of the formula syntax needed to do this with the INDEX function is explained at the following link.

https://excelxor.com/2014/09/05/index-returning-an-array-of-values/

This formula can now be used in the creation of the CF, where CF Formula is =SUM(N(A1=Num_Array)), starting at A1 and applied to all of Column A.

The result of this CF is shown below.

The example file can be downloaded here.

Previously, I discussed the use of a custom list with Data Advanced Filter.

In the article, I only showed one custom list being used in the advanced filter. However, in most situations, a number of specific searches are required. To illustrate this, I combined all of the links from ESL #1-60 in an Excel file. The description of the links are in column A and the links are in column B. The goal is to perform multiple searches with lists while using only a single formula in the advanced filter. A picture of this can be seen in the following figure.

The formula in the advanced filter (B1:B2) on the AdvFilterTable tab is

=SUMPRODUCT(NOT(ISERROR(SEARCH(Choice,A5)))+0)>0

where Choice=CHOOSE(MATCH(AdvFilterTable!$A$2,CritLists,0),PowerBIList,VBAList,ExcelList)

This is the key formula for returning the desired list into the advanced filter formula.It is controlled by cell A2, which uses Data Validation with the following list.

CritLists=LList!$E$2:$E$4

The criteria lists tied to the CHOOSE function are are shown in the following figure.

These are expanding dynamic lists to add additional criteria if necessary.

PowerBIList=OFFSET(LList!$A$1,1,,COUNTA(LList!$A:$A)-1)

VBAList =OFFSET(LList!$B$1,1,,COUNTA(LList!$B:$B)-1)

ExcelList =OFFSET(LList!$C$1,1,,COUNTA(LList!$C:$C)-1)

When a list is selected in cell A2 and the advanced filter is applied to the links table, the result is as shown below.

Functionally, you can search all of my links for the content you desire. Also, you can use this filter model with your own data and criteria. I hope that this is useful.

You can download the file here.

The very 1^{st} 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://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).

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))))

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

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.

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.

Some time ago (16 years – which is 100 in Excel years) I developed a formula solution for counting the number of unique items in a filtered list. I realized that this methodology could be used with the new TEXTJOIN function in Excel (Office 365 version). See the original publication in EEE# 20:

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

The following defined names are needed to construct the formula.

Rge=Sheet1!$A$2:$A$20

unRge=IF(SUBTOTAL(3,OFFSET(Rge,ROW(Rge)-MIN(ROW(Rge)),,1)),Rge,””)

unRge returns an array that contains only filtered items.

In the figure, rows 2,3,4,6,8,10,11,12,15,17,18,19,and 20 are visible. To return a unique delimited string for only those visible rows, use the following formula (in E3):

=TEXTJOIN(“,”,TRUE,IF(N(IF(ISNA(MATCH(“”,unRge,0)),MATCH(Rge,Rge,0),IF(MATCH(unRge,unRge,0)=MATCH(“”,unRge,0),0,MATCH(unRge,unRge,0)))=ROW(Rge)-MIN(ROW(Rge))+1),unRge,””))

which affords

a,c,h,l,v,m,d,w,g,o,t

You can download the file here.

Remember again, that you have to have the Excel version included in Office 365 in order for the TEXTJOIN formula to work.