Monthly Archives: June 2017

Enable #Excel Formulas to Give an Audio Result by David Hager

 

Most people reading this article have seen how to document a formula with the N function. Here is an example.

=SUM(A1:A3)+N(“This formula sums the first 3 values in Column A”)

The technique presented here uses a similar concept (the result is not affected by the additional formula).

However, instead of documenting the formula, it adds the ability to provide an audio result each time the formula is recalculated. I am sure that the use of this UDF will find wide usage throughout the Excel community.

Here is the VBA Function code for doing this. Make sure to add the Microsoft Speech Object Library (sapi.dll version) under Tools, Reference for this to work.

Function GiveVocalResult(Optional Person As String = “Him”, Optional bVolatile As Boolean = False, _

Optional Rate As Long = 1, Optional Volume As Long = 60)

Dim Voc As SpeechLib.SpVoice

Set Voc = New SpVoice

Dim sAddress As String

Application.Volatile bVolatile

With Voc

If Person = “Him” Then

Set .voice = .GetVoices.Item(0) ‘male

ElseIf Person = “Her” Then

Set .voice = .GetVoices.Item(1) ‘female

Else

End If

.Rate = Rate

.Volume = Volume

sAddress = Application.Caller.Address

rResult = Evaluate(Mid(Range(sAddress).Formula, 1, InStr(1, Range(sAddress).Formula, “&Give”) – 1))

.Speak rResult

End With

End Function

After numerous attempts to create the desired UDF, I finally came up with a solution. It is based on these two line of VBA code.

sAddress = Application.Caller.Address

rResult = Evaluate(Mid(Range(sAddress).Formula, 1, InStr(1, Range(sAddress).Formula, “&Give”) – 1))

Obtaining the address containing the formula proved to be the right path. The use of the caller address in the Evaluate function afforded the result of the “first” formula. There are two examples that illustrate the use of the GiveVocalResult UDF. Note that all of the arguments of the function are optional.

In the first example, shown in the following figure, a formula that looks for the second largest value in a range “reads” the result in a female voice.

In D1   =LARGE(A1:A10,2)&GiveVocalResult(“Her”)

GiveV1

When values are changed in the range A1:A10, the result of the formula is vocalized. The use of “Her” in the first function argument changed the default value of Him to Her.

In the second example, the value in cell C2 is set by using a data validation drop down list. When changed, the formula in D2 (=C2&GiveVocalResult()) reads the result in a male voice.

GiveV2

The last 3 arguments in the function are all set with default values. bVolatile is set to False, Rate is set to 1 (can vary between -10 to 10, and Volume is set to 60 (can vary from 0 to 100). Experiment with the settings and enjoy.

Because of what I consider to be a groundbreaking technique, please reference my web site when you use it.

https://dhexcel1.wordpress.com/

The example file can be downloaded here.

VoiceVar

Advertisements

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

 

#Excel Super Links #75 – shared by David Hager

 

Getting A Handle On Userforms [VBA]

https://colinlegg.wordpress.com/2016/05/06/getting-a-handle-on-userforms-vba/

Excel VBE Options

http://www.excelgaard.dk/Lib/VBE/Options/

Excel Solution: Who Should Sit Where?

http://datascopic.net/seating/?doing_wp_cron=1497911478.3255810737609863281250

Basket Analysis in DAX

http://www.daxpatterns.com/basket-analysis/

Excel Short & Sweet Tip #6 (Shuffling a String) by David Hager

https://dhexcel1.wordpress.com/2017/04/25/excel-short-sweet-tip-6-shuffling-a-string-by-david-hager/

 

#Excel Sparklines: Display Last N Values by David Hager

Sparklines are a relatively recent addition to the myriad of tools in Excel (version 2010). They are very useful in creating a number of different ways to visualize data. In this example, creating sparklines from a vertical range of data in a table reveals some interesting options. If you make a data table and create a sparkline from it, the process goes smoothly, generating a sparkline that will auto-expand its chart data along with added data to the table as shown in the figure.

sline1a

But, what if you want to display only the last N values in the table. In that case, you need to use OFFSET formulas as ranges, as first discussed in

https://dhexcel1.wordpress.com/2014/05/04/excel-did-you-know-tip5/

The formula needed to do this (with input from cell F5) is defined as:

LastN1=OFFSET(Sheet1!$A$4,COUNT(TrendData)-Sheet1!$F$5+1,,Sheet1!$F$5)

The method I used to apply this formula range was to create a sparkline from a random selection in the table body and then go back and Edit the sparkline source data as shown below (initially, I had to enter as =LastN1).

sline2

A similar formula was made accepting input from cell F6.

I hope that this technique adds to your data visualization portfolio.

You can download the example file here.

sline1