These days dynamic arrays, power query and DAX seem to be all the rage. But let’s not forget about an old friend: VBA UDFs (User Defined Function). Continue reading
Learn VBA UDF
Chess FEN viewer
FEN notation provides the necessary info to restart a chess game from a given position. See how I created a FEN viewer in Excel! Continue reading
Humidex Formula in Excel
In Canada we have long harsh winters but July was REALLY hot this year! How hot? Let’s combine heat & humidity to determine the real temperature we feel.
Excel ChessGames Viewer
On July 20 I woke up early and started doing tactics on Chess.com I then noticed an email from Robert Gascon with subject heading “Excel ChessGames Viewer“! Robert also reminded me that it was International Chess Day! Continue reading
Auditing & Editing M code (Power Query)
Power Query is a powerful and intriguing tool. However, there are stumbling blocks that make it challenging. Let’s audit and then modify some M code! Continue reading
All Combinations Using Power Query
Given 3 separate lists of text how can we create all combinations? I’ve known Power Query (Get & Transform) can create a cartesian product but it’s only now that it’s part of my active skill set. Continue reading
Sum Cells in Overlapping Ranges
This is a cruel Excel challenge (I’ve used it!). Why? Because there is a brilliantly simple solution to summing cells that overlap two ranges. Continue reading
Subtotal helper column
This formula =COUNTIFS($A$7:$A$309,$AK4,$B$7:$B$309,1) considers all rows within the given range when counting. What if we want to exclude filtered and/or hidden rows? Continue reading
How many unique List 1 names found in List 2?
Imagine two lists of names. We have three questions: (1)how many unique names in List 1? (2)how many unique names in List 2? (3)how many unique names from List 1 are found in List 2? Continue reading
Power Query solution: partial match count with a condition
In a previous post we saw how formulas can solve a partial match with conditions. Now I will solve it using Power Query because my friend Kunle challenged me to do so 🙂 Continue reading