Forum Discussion

StanCub's avatar
StanCub
Copper Contributor
Apr 28, 2023

Is there a formula to aid in organizing the data by count (many) ranges to generate a chart?

I have this (sample raw data) sheet here I need to achieve the following (make a chart) - 

SAMPLE RAW DATA PREVIEW

 

Insitution NameStateCount
Legacy BankMA168
PeoplesBankMA657
The Pittsfield Co-Operative BankMA326

 

- Group "Counts" into ranges of 1-200 and list the associated Institution names in the "name column"

Right now there are many "names" with associated values 1,1,1 2,2 .... and so on.

 

I need to group the ranges so I can see which range has most names. 

 

How can I do this? What formula I can use to organize and generate a chart?

 

I've tried using Pivot but that doesnt do the job in excel, SO I'm a bit at a dead end now

excel
Formulas and Functions
  • SnowMan55's avatar
    SnowMan55
    Bronze Contributor
    May 11, 2023

    StanCub 

    For clarity, I am going to use alphanumeric identifiers for the groups of counts – G01, G02, etc. – and I'll call those CountGroupIDs. To derive the CountGroupID values, add a helper column and put this formula into it (specifically for row 2; copy it down as needed):

    ="G" & TEXT( CEILING.MATH(C2/200), "00" )

    (Blank or somewhat-negative values for Count will be assigned G00. I'm also assuming that Count does not exceed 20,000; if it could, you can just add an extra zero in the last argument, making the IDs four characters in length.)


    That might be enough of an answer to get you to a solution using a pivot table. If not, or if you don't want to use a pivot table…

     

    I would create an additional worksheet, and load up column A with the possible CountGroupID values. (If you want that list to contain only the groups that actually exist, you can use a formula like =UNIQUE(Sheet2!D2:D99999).)  Use the following formula in column B (in row 2; copy it down as needed) to get data for the chart:

    =COUNTIF( Sheet2!D:D, A2 )

    (Of course, replace the "D:D" if you used a different helper column on Sheet2.)


    If you wish, sort that range on the second column (Largest to Smallest). Chart as desired.

     

     

Resources