Tag Archives: formulas

Using Conditional Formatting in #Excel to Highlight the N Closest Values to the Mean of a Range by David Hager

 

Conditional Formatting (CF) is one of the most powerful tools in Excel for visualizing data. Because CF can use formulas as input to the CF process, the ability to create formulas based on different data visualization requirements is important. In this demonstration, the goal is to highlight values in a numeric range that are clostest to the average of that range. So, we first make the range dynamic with the following defined name formula.

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

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

ABS_Range =ABS(NumRange-AVERAGE(NumRange))

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 2nd argument of the INDEX function, which then returns the values corresponding to those deviations. 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.

 CF_Closest1

The example file can be downloaded here.

CF_Closest

 

Excel: Conditional Format Rows in List 1 that are Not in List 2 by David Hager

 

I was watching one of Mike Girvin’s excellent Excel videos today. The technique demonstrated in the video was to use Power Query to extract items in list 1 that are NOT In list 2:

https://www.youtube.com/watch?v=JztEKJ-XkCU

I realized that this was the opposite of the conditional formatting technique I had just published.

https://dhexcel1.wordpress.com/2017/06/24/excel-conditional-format-rows-in-one-list-that-are-in-another-list-by-david-hager/

So, for the sake of completeness, I decided to modifyvmy technique to emulate Mike’s technique.

Only a minor modification of the the CF formula was neccesary to produce the opposite condition.

=ISERROR(FIND(CONCAT($A2:$E2),CONCAT($I$2:$M$6)))

You can see the results in the following figure.

 TableAntiCompareCF1

You can download the example file here.

TableAntiCompareCF

 

Excel: Conditional Format Rows in One List that are in Another List by David Hager

 

Here is the scenario. You have a long list with multiple columns and a second list containing records to look up in the first list. All of the records in each row of the first list must be compared with all of the records in the second list.

Here is the conditional formatting formula that will highlight the desired rows. Although it appears surprisingly simple, it took me quite a while to come up with this.

=FIND(CONCAT($A2:$E2),CONCAT($I$2:$M$4))

This formula looks for the concatenated row string in the long string from list 2 and if the FIND function finds the string, a value corresponding to the position of the string is returned. Any number of 1 and above is interpreted in CF as TRUE. Otherwise, the formula produces an error, which is interpeted as FALSE.

This CF formula was applied to list 1 starting at A2.

As you can see from the following figure, the expected rows in list 1 are highlighted.

 TableCompareCF1

Now, if your data happened to be all numbers in each column, a row could be hightlighted by coincidence due to a match in the large concat string. So, don’t use this technique with lists of that characteristic.

I am sure that you will find this technique useful.

You can download the example file here.

TableCompareCF

Great #Excel Technique: August 2017 Total Eclipse Info by David Hager

 

In case you have not heard, on August 21, 2017, a total eclipse shadow is going to stretch across the entire United States. And, the rest of the U.S. will experience a partial eclipse of vaying degree based on the specific location.

This workbook will direct you to information for a specified location at timeanddate.com for the August 2017 total eclipse of the sun.

It is important to note that this workbook does not in any way purport to access or create any information about the eclipse directly. Rather, it uses a link to the web site following the guidelines of the disclaimer to open a web page with the specified eclipse information.

https://www.timeanddate.com/information/disclaimer.html

The creation of the list lookups used in this example were discussed in this recent article.

https://dhexcel1.wordpress.com/2017/06/22/creating-dependent-lists-from-a-column-lookup-in-an-excel-list-by-david-hager/

Please review that article to see how the control cells C3 and D3 work. When the state is selected in C3, the list in D3 is populated with the desired city. When both are selected, in cell D1 (named The DesiredLink) the array formula:

=INDEX(EclipseLink,MATCH(TheCityName&TheStateName,City&State,0))

which looks up the eclipse link for that state and city.

Eclipse1

When the CommandButton is clicked, the following procedure in the Control worksheet module is run.

 

Private Sub GetEclipseInfo_Click()

ActiveWorkbook.FollowHyperlink Address:=Range(“TheDesiredLink”).Value

End Sub

 

This procedure uses the correct link to access the eclipse web site for the specified statye and city, which is opened in your browser. Hoper that you enjoy this, and the eclipse.

You can download the file here.

2017_Eclipse

Creating Dependent Lists from a Column Lookup in an #Excel List by David Hager

When you have columns in a list that contain (for instance) state names and their corresponding cities, you might find it useful to create lists from the internal range of the subordinate column. For example, in the list shown here you might want to return the range from the City column corresponding to a specified state in the State column shown below.

 LookupFromTable1

The key to generating lists from the City column is the following formula.

TheCity=OFFSET(State,MATCH(TheStateName,State,0)-1,-1,MATCH(TheStateName,State,1)-MATCH(TheStateName,State,0)+1)

where State is the column in the list on the LookupSheet tab containing state names and

where TheStateName is the cell containing a data validation list of unique states (Control!C3).

where TheCityName cell (Control!D4) contains a data validation list of cities corresponding to the return value of TheCity.

Basically, what this formula does is return a range from the City column, given a specified state.

The following figure shows the cells with the data validation drop down lists.

 LookupFromTable2

The information from these cells can be used for further lookups, which is the subject of an upcoming post. You can download the example file here.

LookupFromTable

 

Using #Excel to Make A Round Robin Tournament Schedule by David Hager

The creation of a round robin tournament is a common topic in a Google search of the subject, and there are a myriad of different levels and complexites demonstrated. I decided to make a calculation model that would make this easy to do.

Starting in cell AK2, the names of the teams in the tournament are listed. For every name entered, a consecutive number must also be entered, starting with 0 in cell AL2. See the following figure.

 rr1

And, that’s all you have to do!

The process runs entirely on Excel formulas – no VBA is used.

The resulting tournament schedule is shown in the following figure (partial view).

 rr2

The workbook is completely unprotected, so feel free to discover how to works. There are a number of interesting and complex formulas used in the design of this model, both on the worksheet, in conditional formatting and in defined name formulas.

It is difficult to visualize the entire tournament bracket at once. Perhaps I will leave that issue as an exercise for the user.

The file can be downloaded here.

 RoundRobin

Enjoy!

 

Revisiting Using the Excel TEXTJOIN Function To Return Unique Items From A 3D Range by David Hager

In the following article:

https://dhexcel1.wordpress.com/2017/01/02/using-the-excel-textjoin-function-to-return-unique-items-in-a-one-cell-delimited-string-from-a-2d-and-3d-range-by-david-hager/

I demonstrated a method of obtaining unique items from a 3D range. At the time that this article was written, I was unaware that the TEXTJOIN function accepted native 3D references. See:

https://dhexcel1.wordpress.com/2017/05/23/excel-native-3d-ranges-with-the-textjoin-function-plus-bonus-by-david-hager/

The techniques needed to convert a delimited string to a unique delimited string have already been discussed.

https://dhexcel1.wordpress.com/2017/01/03/creating-a-unique-delimited-string-from-a-delimited-string-excel-formula-method-by-david-hager/

So, with the starting point of a delimited string obtained by using 3D reference with TEXTJOIN, the link above will then allow you to create the unique array or delimited string.

To be clear, first TEXTJOIN is used with a 3D range argument to make a delimited string. Then, that string is used in the formula construction described in the link above to make an array of unique items, which can be subsequently used to make a unique delimited string.

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