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.
Date Archives → 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 ??? Continue reading
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? Continue reading
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? Continue reading
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” Continue reading
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]),””) Continue reading
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. Continue reading
World Happiness Report
Recently I saw a link about the World Happiness Report. What is it? “…a landmark survey of the state of global happiness …which ranks 155 countries by their happiness levels” Can you guess what my first question was? Continue reading
Video 00159 Create unique and sorted text list
We have a bunch of names in a column. We need to create a unique and alphabetically sorted list. BE CAREFUL! Before we jump into a solution we should ask a couple of key questions. Does the solution need to be dynamic? Do we need to teach the solution to our co-worker or client? Continue reading
Video 00156 One Complex Formula or Helper Formulas?
There are many factors to consider when creating a solution in Excel. Are we building the solution for someone else with basic Excel skills? Should we use a complex formula in only one column to save space or use helper formulas to simplify the solution? Let’s take a look at this question from Mike Girvin’s ExcelIsFun YouTube channel. Continue reading