xlCubeSuper An Excel Game Explained – Part1

I have just recently updated/modified my xlCube game that I first started on 20 years ago. The main modification is that it now has 7 cubes that must be destroyed before the game is over. For those who have never played the game, the previous version can be found at:

https://dhexcel1.wordpress.com/2016/11/29/xlcube-an-excel-game/

The new game is called xlCubeSuper. And, like the previous release, I encourage you to dig into the details of how this application was constructed. But, I know that your time is precious, and you may feel that this would not be an effective use of your Excel time. So, this time I am going to explain in detail all of the tricks and techniques used in the making of this game. In Part 1, I am only making it available to you. The explanation will come in subsequent posts. In the meantime, have fun with it.

You can download the file here.

xlcubeSuper

 

Excel Super Links 1-150

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.

Excel Super Links 1-150

 

 

Conditional Formatting Gem in #Excel: Highlight the N Closest Values to the Mean of a Range in a Filtered List by David Hager

 

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

https://dhexcel1.wordpress.com/2017/07/04/using-conditional-formatting-in-excel-to-highlight-the-n-closest-values-to-the-mean-of-a-range-by-david-hager/

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:

https://dhexcel1.wordpress.com/2017/01/08/creating-a-unique-delimited-string-from-an-excel-filtered-list-by-using-the-textjoin-function-by-david-hager/

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.

CF_Closest_Filtered

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 2^nd 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.

 

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.

 

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.

 

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

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

Please contact me for any Excel project work that you need. Written 10/27/2020. 

My email address: dhExcel1@gmail.com

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.

 

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

 

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!

 

#Excel Short and Sweet Tip #18 (Toggle Formatting on Worksheet) by David Hager

You might at some point want to view a section of a workbook without any user-applied formatting. It turns out that this is easy to do through conditional formatting. For this example, A4:G10 has data that has a simple table format applied, as shown below.

 

There is a Data Validation list option in cell B2, with the list in A1:A2 (On, Off). Then, a conditional format is applied to A4:G10 with the formula =IF($B$2=”Off”,1,0).

 

The important part of this process is selecting formatting options that look “normal”, such as no fill, no borders and a black font. When the Off value is selected, the formatting of the table “disappears”, as shown below.

 

You can download the example file here.

ToggleFormatting

#Excel: Creating a List of Option Expiration Dates and Triple Witching Dates with Excel Formulas by David Hager

Those people that closely follow the workings of the U.S. financial market know that options expiration day is important. In particular, triple-witching refers to the quarterly expiration of index futures, index future options and certain stock options on the third Friday of March, June, September and December. The other months having a 3^rd Friday are also important option expiration dates.

=TODAY() ‘in cell A1

=INDEX(A1+ROW($1:$40),MATCH(1,(DAY(A1+ROW($1:$40))>14)*(DAY(A1+ROW($1:$40))<22)*(WEEKDAY(A1+ROW($1:$40))=5),0))+1 ‘formula in A2 and fill down.

This will afford the list of option expiration dates. In order to see triple witching option expiration dates, this conditional formatting formula must be used on the date list.

Triple=NOT(MOD(MONTH(A2),3)) ‘defined when active cell is A2.

For the first cell, a different conditional formatting formula is needed. With A1 as the active cell, define Start:

=OR(A1=INDEX(A1+ROW($1:$40),MATCH(1,(DAY(A1+ROW($1:$40))>14)*(DAY(A1+ROW($1:$40))<22)*(

WEEKDAY(A1+ROW($1:$40),11)=5),0))-28, A1=INDEX(A1+ROW($1:$40),MATCH(1,(DAY(A1+ROW

($1:$40))>14)*(DAY(A1+ROW($1:$40))<22)*(WEEKDAY(A1+ROW($1:$40),11)=5),0))-35)

When the conditional format is applied to cell A1, the characters will appear as bold purple in the model when that date is an options expiration date. In order to see triple witching option expiration dates in A1, this additional conditional formatting formula was used to hightlight bold red text if A1 contains a triple witching date.

=AND(Triple,Start)

You can download the file here.

OptionExpire

 

#Excel: Finding and Visualizing the Last Record in a Table Based on Criteria by David Hager

A tweet by Tom Urtis intrigued me.

https://twitter.com/TomUrtis/status/863092107564638208

I used to play with the 3^rd argument of the MATCH function many years ago, but I gave it up as a lost cause because strange results were returned if the column in question had blank cells in it. However, if used in a contiguous list, Tom demonstrated its utility in the following formula.

=MATCH(2,1/(Table1[Name]=”Urtis”))

If the 3^rd argument of the MATCH is not declared, it defaults to a value of 1. That allows for a lookup that is equal to or less than the 1^st argument. The undocumented feature of the 3^rd argument is that when the value is 1 it does the lookup from the bottom of the data rather than the top. So, Tom’s formula finds the LAST matching item (in cell A44 in the example workbook).

I realized that this technique could be extended to multiple criteria. The following formula shows 2 criteria

=MATCH(2,1/((Table1[Name]=”Urtis”)*(Table1[Type]=”b”)))

and this formula shows 3 criteria.

=MATCH(2,1/((Table1[Name]=”Urtis”)*(Table1[Type]=”b”)*(Table1[Total]<300)))

It is important to note that the criteria can be from any column in a table, and just not adjacent rows.

Note also that the formulas return the row position in the table.

This technique can also be used for visualizing the row matching the criteria by conditional formatting (CF).

This is the conditional formatting formula used for 3 criteria.

Criteria3=ROW()=ROW(INDIRECT(“A”&MATCH(2,1/((Table1[Name]=”Urtis”)*(Table1[Type]=”b”)*(Table1[Total]<300)))+1))

This CF formula is applied to the entire table and highlights row 21 as expected.

As a further extension of this technique, a criteria table can be used that replaces the static criteria with values from the table, as shown below.

=MATCH(2,1/(Table1[Name]=F2))

=MATCH(2,1/((Table1[Name]=F2)*(Table1[Type]=G2)))

=MATCH(2,1/((Table1[Name]=F2)*(Table1[Type]=G2)*(Table1[Total]<H2)))

So, there are a number of ways to use this technique. Thanks Tom, for the idea.

You can download the file here.

LastRowCrit

#Excel: Using Conditional Formatting to Highlight Cells Containing Native 3D Formulas by David Hager

Conditional formatting (CF) in Excel can be used to hightlight cells that meet certain criteria. In this case, I wanted to create a CF that would highlight cells containing formulas that use Excel’s native 3D references. So, this would be like the following example.

=SUM(Sheet1:Sheet2!B2:B5)

So, I tried to determine what was unique this type of formula string compared to others. What I noticed was that the first colon in this formula always comes before the exclamation point. Thus, I started working on a solution on that basis.

Note, though, that there are ways to write a formula containing a 3D reference that will not meet this criteria, such as:

=SUM(C2:C5,Sheet2!C2:C5)

So, don’t use those kinds of formulas. 😊

To lookup the position of the colon in the formula string, the following formula is needed.

=MATCH(“:”,MID(FORMULATEXT(F6),ROW(INDIRECT(“1:”&LEN(FORMULATEXT(F6)))),1),0)

where F6 contains the formula.

The corresponding formula for looking up the position of the exclamation point is:

=MATCH(“!”,MID(FORMULATEXT(F6),ROW(INDIRECT(“1:”&LEN(FORMULATEXT(F6)))),1),0)

By comparing the two formulas, the following Boolean expression wrapped in an IFERROR function is defined as Is3D:

=IFERROR(MATCH(“:”,MID(FORMULATEXT(F6),ROW(INDIRECT(“1:”&LEN(FORMULATEXT(F6)))),1),0)<=MATCH(“!”,MID(FORMULATEXT(F6),ROW(INDIRECT(“1:”&LEN(FORMULATEXT(F6)))),1),0),FALSE)

Applying this formula as a CF on cell F6, you can see that F6 is highlighted as expected.

You can download the example file here.

IsNative3DFormula

Using Conditional Formatting to Highlight Unique Items in an Excel Filtered List By David Hager

 

Quite a while back I created a formula to count the number of unique items in a filtered list. For examples, see:

http://blog.contextures.com/archives/2010/10/04/count-unique-items-in-excel-filtered-list/

and

https://dhexcel1.wordpress.com/2017/01/08/creating-a-unique-delimited-string-from-an-excel-filtered-list-by-using-the-textjoin-function-by-david-hager/

I decided to extend this methodology to the conditional formatting of a filtered list. The following defined name formulas are required.

Rge=$A$5:$A$29

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

cfUnRge=INDEX((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)),ROW()-4)

(The cursor must be on A5 when the cf function is defined and applied to A5:A29)

So, before filtering, the list shows the 1^st unique items highlighted in yellow.

 

After filtering (removing the letters b,e,f,g), the resulting filtered list looks like this.

 

I hope that this is another useful tool to add to your Excel bag of tricks. You can download the file from the link below. Enjoy!

unFiltered

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.

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