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…)

# Formulas

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…)

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…)

**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!

**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)

**Download my Excel file**

Download **here** or via **my OneDrive** (file 00070)

**Watch my YouTube video**

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).

**Subscribe to my YouTube channel and learn more!**

**See my free templates here!**

**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…)

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???**

**Question: Why use an array formula?**

**Answer: Y****ou can answer a complex question using only 1 array.**

In this video, the array uses the EXACT and LEFT worksheet functions. (more…)

**Question: Why use an array formula?**

**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…)