Putting numeric data into bins is a useful technique for summarising, especially for continuous data. This is what underlies histograms which is a bar chart of frequency counts in each bin. There are two main ways of doing this in Excel with groups and with vlookup (you can also do this in SQL).
If you want equal length bins in a Pivot Table the easiest way is with groups.
Right click on the column you want to bin and select
Then enter the start and end of the bin and the length of the bins.
You then get a binned view in the pivot table.
What about if you want irregular size bins, or outside a pivot table?
One way is with nested
IF statements, but this is error prone and hard to maintain.
IF(B2 < 0.1, "0 - 0.1", IF(B2 < 0.3, "0.1 - 0.3", ...
An easier way is to make a separate table for your bins that are ordered.
To get the labels I concatenated the values of the bin endpoints:
=CONCAT(F5, " - ", F6).
I also labelled the region as
Bins (in the top left) to make it easy to refer to.
Then you can lookup the value with
By default it does a
RANGE lookup, returning the first row in the table where the value is at least
For example I used the formula
The first argument is the value to lookup (here I used a column Value of a table, the second is the area we lookup from (the data range
Bins), the third is the column number of the table to lookup the value from (here 2, the
Bin Value) and finally if we want a range lookup (
TRUE - that's how the binning works).
So now you can count your data in custom bins and even plot a histogram over it. The only tricky thing is getting the right order for the bins in pivots/plots (since for example as strings "2" > "10"). I'm not sure if there's a good way to do this in Excel (like an ordered factor in R).