When we are modelling in Excel we often need to enrich our data with value groups.
For example maybe we need to assign the tag "Small" to any animal with a weight of 10kg or lower, "Medium" between 10kg and 300 kg and "Large" for anything over 300 kg.
As always in Excel there are a number of ways to achieve this. Some are better than others!
I start with the IF function, which as you can see allows for one condition and a catch all for everything else. This works really well if you just have two buckets. For example anything less than or equal to 10kg is "Small" everything else is marked as "Large".
However, in the example we need more than one condition so have to nest to IF statements together in order to get the result that we need.
I recommend avoiding nested IF statements as they add complexity to your model and are difficult to review. Once you get past 3 or 4 nested IFs it starts to get very difficult to keep track of your logic so you are more likely to make a mistake and it will start to affect the performance of your file.
Technically there is a limit of 64 nested IFs. The most I have seen was in this formula in a model I was rebuilding:
I challenge anyone to be able to explain what this formula does!
The IFS function was introduced back in 2016 to tackle the issue of nested IF functions.
IFS enables you to declare multiple conditions and the result you would like displayed.
IFS works really well but has a couple of limitations.
Firstly, like IF, you need to declare each condition explicitly within the function. This means that the number of conditions is fixed by the structure of your formula and will need manually updating if the logic changes.
Secondly the final "catch all" argument at the end is a little strange. In the example below, in order to assign anything that does not meet the first two conditions to "Medium" we need to use the logic ,TRUE, "Medium". Failing to provide this final argument would result in a #N/A error being displayed.
=IFS([@[Weight (kg)]]<=10,"Small",[@[Weight (kg)]]>=300,"Large",TRUE,"Medium")
Luckily there is a solution that is simple to build and maintain and provides the user with complete flexibility, especially when built using Excel Tables.
Using the XLOOKUP function with a match mode of -1 (exact match or next smaller item) enables us to use a reference table to manage our value group buckets.
The resulting formula is nice and simple to understand and explain and we can easily add new categories to our model by adding them to the table.
One point of note, you need to ensure that the reference table is set up in ascending order per my example in the video.