Category Archives: conditional formatting

#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 3rd 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.

OptExpir1

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 3rd 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 3rd 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 1st argument. The undocumented feature of the 3rd 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.

RowCrit1

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.

Is3D1

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 1st unique items highlighted in yellow.

 unFig1

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

unFig2 

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.

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