AVERAGE is one of those easy functions in Excel. Like, super easy! It has just one argument, the cells, or numbers to be averaged. So what’s the problem? It’s too easy to get wrong, yet we might not notice for weeks, months, years or ever. Look at the screenshot below. The result of each method … Read more
If you read a lot of Excel blogs and sites, you will see a common topic arise which encourages us all to use the INDEX MATCH formula combination, rather than VLOOKUP. I’m not going to disagree with that recommendation, however I feel the question of “Why is INDEX MATCH better than VLOOKUP?” is often not … Read more
In Excel 2010, Microsoft introduced a new function called AGGREGATE. It is one of the most powerful functions in Excel, yet I’ve rarely seen it used in a real-life scenario. My aim with this post is to show you how good AGGREGATE is, so that you can make use of its power. What does AGGREGATE … Read more
Wouldn’t it be great to change the color of a worksheet tab based on a calculation? If you’re not convinced, here are some possible scenarios: When a schedule reconciles or includes a specific cell value If there is an error in a range of cells To color code tabs to match a contents page You … Read more
Listing the files in a folder is one of the activities which cannot be achieved using normal Excel formulas. I could tell you to turn to VBA macros or PowerQuery, but then any non-VBA and non-PowerQuery users would close this post instantly. But wait! Back away from the close button, there is another option. For … Read more
At the start of this year, I wrote a post entitled Turn a string into a formula with EVALUATE. This post used an Excel 4 Macro as a possible option for turning a string into a formula. These types of Macros were superseded when VBA was introduced in Excel version 5, hence why any macros … Read more
I love using the double VLOOKUP trick to get some lighting fast calculation times in Excel. The drawback is that the list being looked up has to be sorted to prevent errors. If a list were not sorted, it would be great to see a warning. Then the user can take action to sort the … Read more
Have you heard of the mysterious “array formulas”? Do you use them? Or are you like most Excel users, you’ve placed them in the “too hard” pile, never to be seen again. They always seem to crop up in forums as solutions to some tricky formula questions. In this post, I want to introduce to … Read more
Note: This method should only be used for Excel 2013 and Prior. There is a new method using the FORECAST.ETS function. Check it out here. In my opinion, one of the best functions in Excel is FORECAST. We can feed this function values from the past and it will use linear regression to forecast a … Read more
We have now reached the end of the Mastering VLOOKUP series. Hopefully, you’ve picked up a few tips and tricks to take your VLOOKUP skills to the next level. If you’ve been following along you will have learned more than just VLOOKUP: Download the Advanced VLOOKUP Cheat Sheet There is a lot to remember, so … Read more