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!
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).
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
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.
In this video, the array uses the EXACT and LEFT worksheet functions. Continue reading
In this video, I use an array formula to extend the use of Excel’s LEN function. Continue reading
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
Learn how to add numbers in Microsoft Excel using these functions: SUM, SUMIF, SUMIFS. Continue reading
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
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.