# DSUM gymnastics

I explored ideas to make DSUM function’s criteria argument as flexible as a gymnast. Is it a spreadsheet miracle? Maybe. Continue reading

You may never need this…but I did recently and it’s just fun knowing that Excel can solve this! Continue reading

# Flirting with Volatility

I had some questions and improvement ideas for my formula pixel face. I learned a lot playing around with this. Continue reading

# Calculate Hours from Messy Text

I need to calculate hours given data like this in each cell: “9-10, 10-11, 10-11, 12-13, 12-15+, 14-17+ 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

# 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. Continue reading

# 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. Continue reading

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