How To Model Winning And Losing Streaks In Excel
How To Model Winning And Losing Streaks In Excel
Modelling winning and losing streaks for sports data in Excel is something I’m asked about all the time. Once, somebody claimed it isn’t possible in Excel!
The good news: it’s perfectly possible. And, the task can be accomplished using just Excel formulae – so no need for VBA or anything else. In fact, the tricky logic makes it a great Excel beginner modelling challenge. So, download the Excel download file, and let’s get to it!
In the video, I show you how to combine IF formulae in Excel to express the required logic. As always, a steady and systematic approach, testing at every step, is key. I also simplify the task by taking multiple ‘intermediary steps’ towards the ultimate goal. Yes, a great way to create a formula you know is right is by creating multiple formulae you know are wrong – but in a way you can easily understand.
Make sure you download the Excel file and work along with me to complete this tricky Excel task that is bound to boost your Excel modelling and formula-building skills. Let me know how you get on in the YouTube comments.
Topics:
00:00 INTRODUCTION
00:17 Building The Streaks Formula With IF
00:34 Why You Should Take An Intermediary Step
01:14 Quickly Hide A Chart With The Selection Pane
01:35 Conveying Streaks Numerically With The IF Formula
02:22 Find The Max Winning / Losing Streak With IF
02:50 How To Visualise Streaks In A Chart
03:29 How To Format A Chart Axis
03:36 How To Add A Secondary Axis
04:45 How To Change Colour Of Negative Values In A Chart
05:27 About Our Members’ Monday Community
ABOUT MEMBERS' MONDAY
This video is taken from our unique and exclusive Members' Monday learning community.
Looking to build your data analysis skills in a supported environment with expert access, with a group of like-minded individuals?
Need structured materials with a practical focus, and a place to go for help?
Want to take a long-term approach to your learning and get real improvement, rather than lurching from one problem to the next?
You'll love our Members' Monday community!
"Joining this community was a smart investment" - MEMBER FEEDBACK