How do we define a close Super Bowl game?
I was thinking about this on Friday…. It’s got to be more than just the final score point difference.
Sure, an exciting finish like the 1991 Giants vs Bills game is a classic. Continue reading
I was thinking about this on Friday…. It’s got to be more than just the final score point difference.
Sure, an exciting finish like the 1991 Giants vs Bills game is a classic. Continue reading →
Sometimes there are data quality issues and you should examine the data before adding formulas and formatting.
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 →
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 →