Handling cells with multiple data entries

[Emily]

Hi,

I'm trying to create a histogram using bins 0 through 13 and data which
looks like this:
13
1
6
1,4,11
1,3
12
1
10
1
2
4

The instances where there is a comma between entries are when there are
multiple answers to the question. How do I tell Excel to count every entry in
every cell for the histogram?

Also, I've been trying to solve another histogram mystery and thus far no
one on the message board has been able to help. I have data ranging from 0
through 8. I have bins 0 through 8. Yet when I enter this into the Histogram
window, it gives me an output like this:
Bin Frequency
0 1
1.142857143 21
2.285714286 7
3.428571429 5
4.571428571 7
5.714285714 6
6.857142857 1
More 10

First, what's with the decimals? And why "More" instead of "7" and "8"?

Thanks!
Emily

 

[Bernie Deitrick]

Emily,

First, use Data / Text to columns, using commas as the delimiter. LEt;s cay that your data starts in
cell A2, and you can have at most 3 entries in any one cell, so that your final data becomes three
columns wide.

Then set up a range with the numbers 0 through 13 in it - let's say, F2 to F15.

Then select G2 through G15, and type

=FREQUENCY(A2:C1000,$F$2:$F$15)

and press Ctrl-Shift-Enter.

The same goes for your 1 to 8 problem - set up the bins before entering the formula.

See help on FREQUENCY for more....

HTH,
Bernie
MS Excel MVP

 

[Emily]

Thanks. I did what you told me to do, but with the Frequency function it only
returns the frequency for the first bin, not the entire histogram. Also, I
did set up the bins prior to running the histogram, but I'm still getting the
weird results with the decimal points.