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

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

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

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

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

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

# Video 00037 Formulas_Adding numbers in Excel

Learn how to add numbers in Microsoft Excel using these functions: SUM, SUMIF, SUMIFS. Continue reading

# Video 00030 Create a HYPERLINK in Microsoft Excel

A hyperlink is an easy way to jump from one location to another location.  The destination could be to the same workbook, an external workbook or a website.

There are two ways to create a hyperlink in Microsoft Excel: Continue reading

# Video 00029 OFFSET function with combo box

Watch this video to learn how to combine the offset function with a combo box.  This will make it easy to retrieve the desired value from your data.