Author Archives: David Hager

About David Hager

Retired. Now very active in Excel community. Contact me with job opportunities.

#Excel Super Links #15 – shared by David Hager

Issue #15 is here!

Which numbers add up to total? Using Only Excel Formulas

https://excelxor.com/2015/02/10/which-numbers-add-up-to-total-2-multiple-solutions/

Modifying Your List of Recently Viewed Excel Files

http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-modifying-your-list-of-recently-viewed-files/

Daylight Saving Time And Time Zones In M (Power Query)

https://blog.crossjoin.co.uk/2017/03/28/daylight-saving-time-and-time-zones-in-m/

Use DAX and Slicers to Define Thresholds for Sentiment Analysis

http://datachant.com/2016/09/06/use-dax-slicers-sentiment-analysis/

Creating a Unique Delimited String from an Excel Filtered List by Using the TEXTJOIN Function

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/

 

#Excel: Using Conditional Formatting to Highlight 3D Formulas with Defined Names by David Hager

There was a comment on LinkedIn about my post about using CF to highlight 3D formulas

“Since I never use a direct reference (or, come to that, enter a formula without naming the range to which it applies) any 3D reference I might use would pass under the radar. Unless, of course, you have an array UDF which will parse the formula to yield a set of references; in which case can I put in an order?”

Initially, I replied that it was not possible. But, the challenge was irresistable. I started working on the problem and, after a number of dead-ends, I was able to come up with a solution. It required a VBA function to return an array of defined names.

Function DefinedNameArray() As Variant

Application.Volatile

Dim Arr As Variant

nCount = ActiveWorkbook.Names.Count

ReDim Arr(1 To nCount)

For N = 1 To nCount

cPos = InStr(1, ActiveWorkbook.Names(N).RefersTo, “:”)

ePos = InStr(1, ActiveWorkbook.Names(N).RefersTo, “!”)

If cPos < ePos Then

Arr(N) = ActiveWorkbook.Names(N).Name

Else

Arr(N) = “”

End If

Next

 

DefinedNameArray = Arr

End Function

What the VBA function does is return an array of defined names, but only places the items meeting the correct criteria for a 3D formula in the final array (which is the same concept using in the initial article).

https://dhexcel1.wordpress.com/2017/04/24/excel-using-conditional-formatting-to-highlight-cells-containing-native-3d-formulas-by-david-hager/

In this case, the InStr function was used to locate the positions of the first colon and exclamation point in the RefersTo string and the values are compared. If cPos<ePos, then the name is added to the array and a null string added otherwise. This array is used in the following formula to find if a 3D defined name is part of the string returned by the FORMULATEXT function. It was defined for use as a CF formatting formula, as shown below (F5 was the active cell when defined).

Is3DDefinedName=MATCH(TRUE,IFERROR(FIND(IFERROR(DefinedNameArray(),””),FORMULATEXT(F5))>1,FALSE),0)

Is3ddn1

Both F5 and F7 contain formulas using 3D defined ranges.

Peter, thanks for the challenge!

You can download the example file here.

CFDefinedNames

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

The ability to randomly scramble a string using Excel formulas does not appear to be possible without helper cells. So, a VBA procedure is needed to accomplish this.

Originally posted at:

https://www.mrexcel.com/forum/excel-questions/37340-word-scramble.html

this VBA function procedure uses a string as the argument and shuffles that string. Copy/paste this procedure into a module in the VBE.

Function ShuffleString(s As Variant)

On Error Resume Next

Dim CL As New Collection

Application.Volatile

ShuffleString = “”

Do Until CL.Count = Len(s)

R = Int(1 + Rnd * Len(s))

CL.Add R, CStr(R)

Loop

For i = 1 To CL.Count

ShuffleString = ShuffleString & Mid(s, CL(i), 1)

Next

End Function

So, the string in A1 is rearranged with =ShuffleString(A1) entered on the worksheet. For example, the string “evert” is shuffled to “rteve”.

#Excel Super Links #14 – shared by David Hager

I will likely end this daily series of Super Links at issue #20, but will continue to add issues as available.

Calculating Pearson Correlation Coefficient using DAX

http://blog.gbrueckl.at/2015/06/calculating-pearson-correlation-coefficient-dax/

Compare equivalent periods in DAX

https://www.sqlbi.com/articles/compare-equivalent-periods-in-dax/

Excel udf: Fuzzy lookups

http://www.get-digital-help.com/2011/04/04/excel-udf-fuzzy-lookups/

Extract Data from a Mixed Column with Power Query

http://www.excelguru.ca/blog/2016/11/30/extract-data-from-a-mixed-column/

Scaling in-cell charts with an Excel formula

https://dhexcel1.wordpress.com/2014/01/07/scaling-in-cell-charts-with-an-excel-formula/

 

#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

#Excel Super Links #13 – shared by David Hager

Thanks for visiting my site to see the latest super links.

Adding a Description and Category to your User Defined Functions in Excel

http://www.ozgrid.com/VBA/DesciptionToUDF.htm

Deep Dive into Power Query Formula Language

https://yotu.be/KXxUDWwo0pg

Tom’s Tutorials For Excel: Conditionally Formatting Locked and Unlocked Cells

http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-conditionally-formatting-locked-and-unlocked-cells/

Excel’s CUBE functions Explained

https://wessexbi.wordpress.com/2015/04/29/passbac-2015-revisited/

Using the Excel TEXTJOIN Function To Return Unique Items In A One-Cell Delimited String From A 2D and 3D Range

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/

 

Excel Short & Sweet Tip #5 (Hiding Error Triangles) by David Hager

Would you like a way to remove those green error checking triangles from worksheet cells, yet retain error checking? Go to File, Options, Formulas, Error Checking. Leave the “Enable background error checking” box checked, and change the Indicate errors using the color (default is green) to white. Of course, this only works if the cells are white. To turn off the error checking and the green triangles, change the “Enable background error checking” box to unchecked.