 ## 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?  (more…) ## CAGR in Microsoft Excel

Recently I was asked “How can you prove that the CAGR answer is correct?“. In this post I’ll show you how to verify the CAGR result. I’ll also review CAGR basics and pitfalls. (more…) ## Video 00172 Unique Values for Data Validation Drop Down

We need to extract unique values from a list to be used in a data validation drop down list. There are many ways to solve this. The best solution depends on asking the right questions. (more…) ## Video 00081 Count and find different types of Excel errors

### Have you ever inherited a messy Excel file?

It can be REALLY painful!  Over the years I have used various techniques to audit Excel files.

This post shows you how to distinguish between different error types, count them and find them! ## Video 00070 Use array formula to solve an algebra equation

### Here is an Algebra equation:  9x – 7 = 47

If we know the value of x we can easily determine if the left side = the right side.

But in this case x is not a single value but rather a Domain (a set of numbers).  x can be 5, 6, 7 or 8.  We have four chances to get a true answer.

Here is the Excel formula —>  {=OR(9*{5,6,7,8}-7=47)}

### Here is what happens inside the formula:

Step 1:  Multiply the 9 with each number inside of the array constant:  {=OR({45,54,63,72}-7=47)}

Step 2:  Subtract 7 from each of the numbers inside of the array constant: {=OR({38,47,56,65}=47)}

Step 3:  Compare each number with 47 (TRUE means it’s the same):  {=OR({FALSE,TRUE,FALSE,FALSE})}

Step 4:  The final answer gives us TRUE (The OR function just needs 1 TRUE)

See how I caught the issue and several ways to fix it.

### What are the ingredients to my solution?

OR function, constant array (that contains the 5,6,7,8), entered as an array formula that requires Control Shift Enter (not just enter).

### See my free templates here! ## Video 00058 Filter Out Number (digits in any order)

### Imagine a column of numbers. Each number is 3 digits in length.

If we select 127 then we want to also hide 127, 172, 217, 271, 712, and 721 (six unique orders).

In this video I show you two possible solutions.  (more…) ## Video 00057 Find the Monday before the oldest date

We can use Excel’s MIN function to find the minimum number in a range. As date values are technically numbers, the MIN function can show the oldest date.

### But….how can you find the Monday before the oldest date??? ## Video 00044 Array Formulas_Example2_EXACT LEFT_functions

### Answer: You can answer a complex question using only 1 array.

In this video, the array uses the EXACT and LEFT worksheet functions. (more…) ## Video 00043 Formulas_Array Formulas_Example1_LEN_function

### Answer: Because you can answer many complex questions using only 1 array formula!

In this video, I use an array formula to extend the use of Excel’s LEN function. (more…) ## Video 00042 Why would I need an array formula?

### Why would I need an array formula?

I have heard this question so many times over the years! If you have never ever used an array formula than this video is for you.

Don’t worry….No prior knowledge is required. I explain things slowly. (more…)