You created a cool array formula and then merged it across several cells. Later, you realize that you need to modify this array formula. When you try to press Control Shift Enter you get this horrible error: Continue reading

# Posts Tagged → Array Formulas

# Excel Pivot Tables Analyze Taco Data!

During our recent vacation to New York City my wife and I loved eating tacos at **TACOMBI**. However, **when I saw the menu it reminded me of a common data challenge!** Continue reading

# Video 00158 Keyword Data Validation List

**Is your data validation list is so long that the endless scrolling makes you want to close Excel and never use it again?** This post will review several solutions to this problem. 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 00137 Vlookup_Returns Multiple Values

Vlookup returns only 1 value, right? Well…normally yes but there are some tricks to return multiple values (numbers) and add them up. Continue reading

# My Favorite Excel Tips From Other Sites Part 1.

I work full time as a Data Analyst currently specializing in Microsoft Excel and Microsoft Access. To keep up to date I watch a fair amount of YouTube videos and follow several websites. Continue reading

# Array Formula Examples

**Why do you use array formulas?**

**Why can’t we use a normal Excel function?**

**What does an array formula do?**

People often ask me questions like these. A quick answer is that **array formulas can be used to answer very complex questions about data**.

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

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

# Video 00069 Are my Excel formulas dragged down far enough?

**A Classic Excel Problem**

In large spreadsheets if you drag formulas down too far then you are increasing the calculation time and also the chances that Excel will freeze and/or crash. Continue reading