# Grouping data into defined bins using VBA

• I've about 30,000 recordes in Column A of numbers between 1 and 100. In Column B I'm looking for a formula to return "Less than 10", "10-20" etc up to "90-100".

I think I need to use excel vba If...Then...Else, but I'm not sure how to get to the next step.

Any help would be much appreciated.

• Re: Grouping data into defined bins using VBA

maybe:
=INT(A1/10)*10&"-"&(INT((A1+10)/10)*10)

Though depending on your goal, a pivot table also seems like a sensible option

• Re: Grouping data into defined bins using VBA

Hello GuyS,

Welcome to Ozgrid.

Do you mean you want to set up bins in increments of 10 (1-10, 11-20, etc.) and get a count of how many numbers fall into these bins?

If you manually set up the bins for each range, you could use a simple COUNTIFS formula.
Example:
To count all numbers less than 11 (for range 1-10) we could use: =COUNTIF(A2:A100,"<11")

O, to count the numbers in the range of 11-20: =COUNTIFS(A2:A100,">10",A2:A100,"<21") or =SUMPRODUCT(--(A2:A100>10)*--(A2:A100<21))

As Kyle23 suggests, a Pivot Table should work for this.

Consider uploading a sample workbook (exact structure, dummy data) to provide an accurate context and some content to work with.
Show examples of your desired results and include a clear and explicit explanation of your requirements, logic, etc.

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!