So many times vlookup (or index & match) saves the day and gets your answer! ….wait…are you sure it’s correct??? Read this post to see why you should use countifs to confirm your vlookup. Continue reading
Date Archives → 2019
CAGR in Microsoft Excel
Recently I was asked “How can you prove that the CAGR answer is correct?“. In this post I’ll show you how to verify the CAGR result. I’ll also review CAGR basics and pitfalls. Continue reading
Extract Largest Number (Power Query & Flash Fill solutions)
In a recent post I audited my colleague’s amazing array formula. It extracted the largest number from as420lkjs09nsdk2324lkjsd099 See other solutions suggested by YouTube viewers! Continue reading
unstacking-data-with-chandeep/
I saw Chandeep’s Data Cleanup Challenge and it looked like fun. Others solved it using Power Query (some amazing solutions) but I used good ol’ fashioned formulas! Continue reading
Largest Number Inside Alphanumeric String
A friend shared this challenge: extract the largest number from this text as420lkjs09nsdk2324lkjsd099 ! The answer is 2324. How do we solve this? Formulas? VBA? Continue reading
Valentine’s Day in Microsoft Excel
How can you improve your VBA skills? Build something! Anything! Even if it’s ridiculous. I needed to practice and ended up creating a robot and a bunch of random style hearts! 🙂 Continue reading
Why and How to Unpivot Data
Before you analyze your data it’s important to decide if you need to unpivot it. Why unpivot? What’s unpivot? Are there exceptions? Continue reading
Rearranging Data
Rearranging data is initially about recognizing the pattern. This screenshot is from Ken Puls. I instantly saw the steps to rearrange the data (thanks to Ken’s online course!) Continue reading
Dynamic Ranges Using Index Function
Robert H. Gascon’s comment from my last post convinced me that I should be using the INDEX function for dynamic ranges not the volatile OFFSET function. Continue reading
What is this formula doing?
=SUMPRODUCT(MID(G2,{1;2;3},1)*1) I found this formula in a notebook. Can you understand it’s purpose? I’ll explain it in this post. I’ll also expand it for a more complex case. Continue reading