In this three part blog post I want to show you how to create formulas that will allow you to automatically calculate the number of courses done between dates and also summarize results.
The data set is based on a real anonymised data set and has my beloved hobbits. It has quite a few bits in it. I used ChatGPT to generate the file and you download the raw version here.
I wanted to show the following – but also to allow for specifying dates. I am going to do it in three posts
- Courses Analysed by Name and Test Date
- Courses Analysed by Result – Part Two
- Courses Analysed by Result % – Part Three
Courses Analysed by Name and Test Date
When I was preparing this I found myself thinking about how I would approach it. So I have decided to show you two approaches. This one (formula based) is more complex than the pivot table driven ways I am going to show you in Part Two and Part Three but it struck me that sometimes it is useful to have a few approaches.
- Extract a unique list of (sorted) course names
- Use a formula to count the number of courses
- Sort the entire data set by number of courses
- Add a chart
The functions I have used in this tutorial are:
You can download the start file here and the finished file here
One Response
The way you do it (Select first and press arrow down) will stop at the first empty row, if there is any.
Why not click on the top half of the table header to select all rows in that column of the table? You avoid the problem I described.