1
$\begingroup$

I should not be here, I am a designer by trade and all these numbers scare me. I am attempting to help someone display their information; they sent me an excel spreadsheet and asked me to make graphs out of certain information.

Unfortunately the spreadsheet contains ~13,000 rows and 32 columns, and I need to find out how to group them so I have all 0-25 values in one bar, 26-35 in another, etc.

I do not know how to even begin to calculate this information or organize it (other than by hand) can someone point me in the right direction as to what I need to do?

$\endgroup$
  • $\begingroup$ Are you asking how to convert arbitrary positive values (such as 17.9, 0.2, 31.0, etc.) into groups (aka "bins") in Excel? $\endgroup$ – whuber Apr 2 '12 at 16:11
  • 1
    $\begingroup$ If you just highlight the data you're interested in displaying and then click on insert you'll be presented with a bunch of options you can experiment with.You need to describe more precisely what you want your graph to represent, e.g. the graph will display a single variable. the x-axis will break the range of the variable into bins and the y axis will represent the number of cases which fit in each bin. $\endgroup$ – Michael Bishop Apr 2 '12 at 16:31
  • $\begingroup$ +1 for facing your fears :) There's a decent description of how to create a histogram (which is what whuber is talking about) here: vertex42.com/ExcelArticles/mc/Histogram.html $\endgroup$ – naught101 Apr 3 '12 at 9:45
  • $\begingroup$ Actually, Naught, I'm not talking about histograms at all: I'm just trying to understand what the question is asking! If it isn't clarified--either by the OP or by some generous reader who succeeds in understanding what is being sought here--it will have to be closed as either off-topic (Excel-centric, non-statistical questions belong on SO) or too vague to answer. $\endgroup$ – whuber Apr 3 '12 at 16:17
1
$\begingroup$

It sounds like you want to "bin" your numbers to create a histogram (like whuber and naught101 mentioned). Without seeing an example from your actual data, I'm guessing you'll need to add one more column for each set of bins you want to create (depending if they're the same for all of your data columns). Based upon your example, you'd have a column of values like 25,35,45,55... (or whatever interval makes sense. That becomes the bin array for Excel's Frequency formula (and the data lables for your chart's category axis). Then, use the frequency formula for each data and bin combination you need (and these need to be array entered, i.e. control-shift-enter). The resulting columns then become the data for your histogram. Good luck!

| cite | improve this answer | |
$\endgroup$
0
$\begingroup$

You could also use a pivot table, though I think you would need all values to be binned together in the same column. Drag this field to both the data area and the rows area. Use count rather than sum for the data field, and then right click on the row field, choose Group, and select the grouping that makes the most sense.

| cite | improve this answer | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.