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.


    Thanks in advance.

    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.


    Please be more explicit about the results you want.
    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.

Participate now!

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

Register Yourself Login