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