Tag Archives: formulas

#Excel Short and Sweet Tip #17 (Returning a Letter Grade Based on a Normal Grading Scale Without Lookup Table) by David Hager

Most of the published methods that convert a numeric grade to a letter grade either use a lookup table or do not account for + or – scores. The following formula overcomes both of those limitations. For the following formula, the numeric grade is in cell A5.

=IF(A5>=90,”A”,IF(A5>=80,”B”,IF(A5>=70,”C”,IF(A5>=60,”D”,”F”))))&IF(A5>98,”+”,IF(A5<59,””,IF(OR(RIGHT(A5,1)={“1″,”2″,”0″}),”-“,IF(OR(RIGHT(A5,1)={“8″,”9″}),”+”,””))))

 

#Excel: Project Tracking Workbook by David Hager

This workbook is one of many Excel projects I have worked on over the years. I am sharing it here for the first time. I am not going to discuss how this application works but rather offer it up to the user as both a useful tool and an opportunity to explore the formulas used in its construction. It is important to note that the workbook is completely unprotected and contains no VBA code.
The ProjSetup worksheet is for data input. Column A is for the names of the projects to track. Column B is for the starting date of the project and Column C is for the finish date of the project. Column D is the priority of the project – low, medium or high. A Data Validation list allows for those values to be selected, and conditional formatting determines the color. Column F has an option box to select between days and weeks. The following figure shows these features.

xlProj1

The ProjTimeDisplay worksheet shows the project time schedules. This was constructed with Excel formulas and conditional formatting. Feel free to dig in to see how it works. Here is a figure to visualize the output.

xlProj2

The project workbook can be downloaded here.

xlProj

#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 Short and Sweet Tip #15 (Using TEXTJOIN With Non-Contiguous Ranges) by David Hager

If you look at the documentation of the TEXTJOIN function found at the following link,

https://support.office.com/en-us/article/TEXTJOIN-function-357b449a-ec91-49d0-80c3-0e8fc845691c

you will find that there are multiple text arguments that are optional for TEXTJOIN. They have the same properties as the 3rd argument, which allows for an array of strings. So, the following formula has 3 non-contiguous ranges as its last 3 arguments.

=TEXTJOIN(“,”,TRUE,A1:A10,C1:C10,E1:E10)

which affords

a,b,c,a,b,c,a,b,c,a,c,d,e,c,d,e,c,d,e,c,e,f,g,e,f,g,e,f,g,e

 TEXTJOIN_NCR

Note that this comma delimited string obtained from the TEXTJOIN function is the starting point of the technique demonstrated in this article.

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

So, for example, you can use non-contiguous ranges as the starting point for making an unique array or unique delimited string. Pretty neat and powerful stuff!

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

 

#Excel Short and Sweet Tip #14 (Generating Random Initials) by David Hager

Sometimes in the construction of a spreadsheet model, you may need to fill a range with something to represent people. Here is a formula that you can use to generate random initials.

=CHAR(INT(RAND()*26)+65)&CHAR(INT(RAND()*26)+65)&CHAR(INT(RAND()*26)+65)

Type this formula into a cell and copy it by using the fill handle into the # of cells desired. These initials will change each time the spreadsheet is recalculated unless you freeze the values. This can be done by selecting the range containing the initials and choosing EDIT || COPY, then EDIT || PASTE SPECIAL.Click the values option and press Enter.

This formula does not calculate as (I) expected. In my experience, the use of the RAND function in a formula can only return one random number. However, in this case, Excel apparently calculates each of the 3 parts of the formula as a distinct formula.

#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: Building a Frequency Summary Table Based on an Excel List by David Hager

After publishing a tip on the most frequent item in a list

https://dhexcel1.wordpress.com/2017/05/09/excel-short-and-sweep-tip-11-most-frequent-item-in-column-by-david-hager/

I decided to build a model based on frequency.

The first formula used is the aforementioned most frequent item formila.

=INDEX(ListRange,MODE(MATCH(ListRange,ListRange,0)))

where ListRange = A2:A16

Then, another formula was needed to return subsequent frequent items,

=INDEX(ListRange,MODE(IF(COUNTIF($E$2:E2,ListRange)=0,MATCH(ListRange,ListRange,0))))

entered into cell E3 and copied down.

The formula to return the number for each item was entered in F2 and copied down.

=COUNTIF(ListRange,E2)

The amount spent on each transaction type was calculated the formula placed in G2 and copied down.

=SUM(IF(ListRange=E2,Total,0))

where Total = B2:B16

Finally, information can be used columns F & G to make a formula that will return the average for each transaction type.

=ROUND(G2/F2,2)

The finished table is shown in the following figure.

 Freq1

The frequency table file can be downloaded here.

FreqItems