Binning numbers and rounding numbers are two separate things in Microsoft Excel. However, there is one interesting binning twist that can be solved using a rounding function.
Excel File Examples
Download my Excel file to follow along.
The Twist
Are you familiar with binning? If not read this post.
Once you understand the concept and learn how to use MATCH or VLOOKUP with the TRUE argument you’ve got it. The twist is when someone asks you
I want to get the absolute closest bin
Given the bin ranges below a 79 falls between 60 and 80. As 79 doesn’t hit 80 it falls back to 60 and gets binned as “High” not “Highest”.
The Solution
If the bins always increase by the same amount (i.e. 20) we can use Excel’s amazing MROUND function to solve this. Formula examples:
=MROUND(79,20) this gives us 80 (“Highest”)
=MROUND(97,20) this gives us 100 (“Perfect Score”)
=MROUND(81,20) this gives us 80 (“Highest”)
Now that I think of it…this never really was a binning question…it’s was a rounding question all along!
Another Twist
As far as I know, the MROUND function solution for absolute closest match only works when the bin range increment (20 in case above) is the same. What if we have bins ranges like this?
See sheet ‘Unequal sized bins‘. Do you have a better solution? Please comment below.
The Dangers of Binning
Binning is incredibly useful but be careful! There are some pitfalls. I once found an error in an Excel template posted online by a very famous company. At first they wouldn’t believe me but I insisted and they finally corrected the error. Read this post to learn more.
Lame Data Jokes
What did the Excel guru say when asked if he knew how to bin numbers? “Bin there done that“.
Why didn’t the lazy data analyst round the data? “He never got aROUND to it“.
What did the German auditor say after seeing a vlookup with syntax errors? “Vlook away“.
Jordan Goldmeier loves Excel jokes. Are these good enough or lame enough for his collection?
About Me
I’ve “bin” a Data Analyst since 2001. I have worked with Databases and spent 1000s of hours working in Excel 🙂
Excel is the most common data tool in the world. I love it !
Do you want to learn more about Excel? Check out my recommended training.