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.
People often ask me questions like these. A quick answer is that array formulas can be used to answer very complex questions about data.
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!
Watch as Bill Jelen (aka Mr Excel) helps Dean: Mr Excel helps Dean Pelton
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)}
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 here or via my OneDrive (file 00070)
See how I caught the issue and several ways to fix it.
OR function, constant array (that contains the 5,6,7,8), entered as an array formula that requires Control Shift Enter (not just enter).
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 →
You’re trying to compare two lists of data to see what items from ‘List B’ are in ‘List A’. Sounds simple enough, right?
But some of the lookup values are not found even though you can clearly see the value in ‘List B’.
Sometimes our data isn’t perfect and we just have to deal with it. In this post you’ll see an awkward data-set from Mr Excel with a VBA solution from Bob Umlas and a formula solution from me. Continue reading →
Imagine a data validation drop down list with many names that are not sorted. Trying to find a particular name in that list would get frustrating as the list grows.
Did you know that there are ways to sort text in Excel? Continue reading →
This Excel file is more of a custom solution than it is a template.
Because, you can learn a lot about Excel by auditing formulas!
Take apart the template and see how it works (password is ‘test’). Continue reading →