29 Aug 2017

## Video 00164 Excel Magic Trick 1452 Max Monthly Customer

Customers can make multiple purchases per month. What is the maximum amount that a customer has spent in a single month? This was a video from the amazing Mike Girvin (Excelisfun). The question is clear, but how did Mike solve it? How would you solve it? (more…)

23 Aug 2017

## Video 00165 Show Column Header for Matrix Value

Last week I saw an interesting Excel puzzle from Leila Gharani on YouTube. Leila solved it using a sumproduct function. I made a video showing several different solutions. Wasif Mustafa sent me his 3 helper column solution. Then Oz du Soleil created a video showing two solutions. So many ways to solve it. (more…)

27 Jul 2017

## Video 00163 Creating a Weighted Random Dataset

It’s easy to create some random data using RANDBETWEEN and INDEX functions. But what if you want to create random data that favors or repeats some values over others? People have told me that they spend time copying and pasting or manually dragging the values to repeat them. Keep reading to see how we can automate this with just four easy steps! (more…)

01 Jul 2017

## 2016 Rio Olympics Women’s Marathon

One of the highlights of my life was travelling to the 2016 summer Olympics in Rio de Janeiro to watch my sister, Krista DuChene, run in the women’s marathon. Her story is inspirational. Krista fractured her leg on April 28, 2014 during a half marathon and then in August of 2015 she broke a bone in her foot only one year before the Olympics.

26 Jun 2017

## I SEE “XLFN” !?!? WHAT IS HAPPENING?

Despite how amazing Excel is there are times when you’ll find yourself shaking with fear! For example, have you ever seen anything as scary as this  =ISERROR(FIND(_xlfn.CONCAT(\$A2:\$E2),_xlfn.CONCAT(\$I\$2:\$M\$6))) What is xlfn ??? (more…)

26 Jun 2017

## Video 00162 Excel Formula Calculates Value of Chess Pieces

I visit many different Excel blogs. Every once in a while I learn something completely new that really boggles my mind! And in this case the Chess world overlaps the Excel world so it was REALLY amazing (and nerdy)! How do I start to explain how awesome this is? (more…)

19 Jun 2017

## What is the easiest way to calculate grades in Excel?

Although the VLOOKUP versus INDEX/MATCH debate gets most of the attention there are MANY more topics to debate in Excel. When calculating student grades what method is best: NESTED IF  or  LOOKUP/BINNING TABLE? (more…)

17 Jun 2017

## Video 00161 Extract Matrix Non Blanks Into 1 Column

Have you ever visited excelxor.com ? It’s an amazing blog with a lot of Excel formula challenges. Here’s one challenge: “given the the 6-by-4 array on the left we may wish to create the single-column on the right(more…)

11 Jun 2017

## Reverse Partial Match Lookup to Filter for a List

By default, Excel’s SEARCH function looks for one value inside of one cell. Jon Acampora from ExcelCampus (one of my favorite blogs) demonstrates how to look for various values within one cell with this formula:

=IFERROR(LOOKUP(1E+100,SEARCH(tblFilterList[Filter List],[@Name]),tblFilterList[Filter List]),””) (more…)

26 May 2017

## Video 00160 Sum Values From Qualifying Mini Tables

Sometimes life is stranger than fiction. If we were to create this requirement as a hypothetical case many would say that it would never happen. Well, this was a real question that someone had for me. Read this post to see the requirements and how I solved this. I’ve also included alternative solutions from the Excel community. (more…)