Excel Frequency Formula: Excel’s Most Difficult Formula?
Click here to download the Excel file for this video.
BY CHRIS MORTIMER
I think of Excel learning as a journey, and I have certainly been on a journey with the Excel Frequency formula!
From being intrigued by it, confused by it, ignoring it altogether and then relearning it step-by-step, I have come to appreciate its unique power. We have to go through a number of steps to get it working properly – without these steps, it doesn’t quite work, which creates frustration and a reputation of being ‘difficult’. In fact, I think it is the most difficult Excel formula to get to grips with. It’s a real shame that many people, like me, have struggled with it: we are missing out on an Excel gem that creates powerful, visual analyses that quickly ‘tell the story’ of datasets.
In this article and accompanying video, we explore five common issues with the frequency formula, and look at how to fix them. We explore the key concepts and critical technical issues. An appreciation of both will position you to take advantage of this incredibly powerful Excel formula. So, what are those five issues? Let’s dig in to number 1.
Excel Frequency Formula: Five Common Issues
1. CONCEPT NOT UNDERSTOOD (DATASET / BINS)
It is easy to blame Excel when things are not working – I have done so many times. The formula / coding are too complicated, the interface is not user-friendly etc. etc. In fact, when people struggle with Excel, the cause is usually conceptual, not technical. By that, I mean that we lack a clear idea in our own heads of what we are trying to do, or how the functions actually work.
I encourage the people I work with to ‘speak out loud’ what they are trying to do with formulae, and / or to articulate in words what a formula does. Technical proficiency (ie. being able to build the formula) comes when conceptual understanding is clear. In the first part of the video, Chris explains the two key concepts, datasets and bins, with reference to an example from the world of running!
2. BINS NOT UNDERSTOOD / ACCURATELY DEFINED
The Excel Frequency formula relies on the concept of ‘bins’ that we define in a single column as single values. Single values? How on earth does that work?! Surely Excel would need to know two values, the upper and lower bounds of the ‘bin’?
Fair question! Excel actually interprets the single values as ‘upper’ bounds and uses this idea to construct the bin ranges. The best way to think of the single values in the bins column is ‘upper bounds’; and, the best way to understand this idea is to download the Excel file and check out the ‘Explainer’ sheet. Bear in mind that the formula looks at the single values in column B, not the ranges as defined in column C – these are included as a learning aid.
This bit can be difficult – it took me years to work out. Take your time and expect some frustration.
Now we have grappled with the conceptual side, let’s consider the technical points…
3. FORMULA ENTERED INTO ONE CELL / ABSOLUTE REFERENCES ($) NOT USED
4. CTRL + SHIFT + ENTER ENTRY TECHNIQUE NOT USED
Points 3 and 4 refer how to construct the formula and to ‘enter’ it into Excel. There are a number of critical points here and, if any one is ignored, the formula is unlikely to work. The only way is to step back, take a steady and systematic approach, and be ready to start again! Follow along with Chris’ detailed explanation in the middle of the video.
5. DIFFICULT TO AMEND / TWEAK = FRUSTRATION!
The final humiliation! Once entered into Excel, the formula can be difficult to remove / amend. This is because it is an ‘array’ formula and, as with data tables, we cannot delete only part of it. In order to remove the formula, we have to select all of the cells in which it is entered. If you need to amend the formula, I recommend deleting all of the formulae and ‘starting again’. I told you it might not be straightforward!
Download the example file, work along yourself, and feel the power of the Excel Frequency formula. If you can get it working, the rewards are immense: the formula helps you to quickly understand the main features of a continuous numerical dataset. As Chris demonstrates, the formula can be used as the basis for chart that is bound to create value for your clients. Good luck!
How did you get on with the Excel Frequency formula? Is it really ‘Excel’s most difficult formula’? Leave a comment on the YouTube channel.