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
Are you a small business owner or are you thinking of starting a small business?
Do you wish you had more hours in a day?
If you answered yes to both of these questions then you should do an audit of all of your tasks to see where you can save time.
I created a free template called ‘Small Business Outsourcing Template’ that will help you determine where you need help.
You can download the file here or here from my OneDrive. Feel free to clear the data input range in sheet ‘3 Task Input’ and add you own data.
I also created a video that shows you how to use the template. Watch the video
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