Histogram Question

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

Hello,

Using W7 and Excel 2007.

Made a Histogram from data for the first time.
Would like to ask two questions re:

a. For the Range for the data column that is plotted, I have many blank
cells at the end of the column.

But, I have also also selected these blank cells when I defined the Range.

However, once the Histogram is plotted out, and I subsequently put
value(s) in these blank cells, the Histogram does not seem to update.

Should it ?
(when I make a conventional Scatter type of graph, it does update
automatically if new additional values are inserted).

If possible, how can I have it update when I put some new values in ?

b. Is there any reasonably straighforward way to have a "Bell-Shaped"
curve show up over the (bin) columns; e.g., one that looks like a normal
distribution (assuming it roughly is) ? e.g., some kind of distribution
curve for the actual data superposed over the columns of the Histogram ?

Thanks,
Bob
 
a.  For the Range for the data column that is plotted, I have
many blank cells at the end of the column.
But, I have also also selected these blank cells when I defined
the Range.
However, once the Histogram is plotted out, and I subsequently
put value(s) in these blank cells, the Histogram does not seem
to update. Should it ?
Click to expand...

How did you create the histogram? Did you use the Data Analysis tool?

Note: I speak XL2003, not XL2007. XL2007 might call the tool
something else. The point is: are the frequencies in the histograms
an array formula or constants?

I suspect the latter. Do not use the Data Analysis Histogram tool.
Instead, if the data are in B1:B1000 (some of which are empty), the
bins are in D7:D17 and you want the frequencies in E7:E17, select
E7:E17, and enter the following array formula [*]:

=FREQUENCY(B1:B1000,D1:D16)

Then, when you modify any value in B1:B1000, E7:E17 will reflect the
change.

Note: I did not include D17 on purpose. See below. But by select
E7:E17, E17 will correspond count everything greater than the value in
D16.

[*] Enter an array formula by pressing ctrl+shift+Enter instead of
just Enter. Excel will display the formula surrounded in curly braces
in the Formula Bar, i.e. {=formula}. You cannot type the curly braces
yourself. If you make a mistake, select the entire range (E7:E17),
press F2 and edit as needed, then press ctrl+shift+Enter.

b. Is there any reasonably straighforward way to have a
"Bell-Shaped" curve show up over the (bin) columns; e.g.,
one that looks like a normal distribution
Click to expand...

This is how I do it, given that you followed the instructions above.

Suppose your data are in B1:B1000. Put =AVERAGE(B1:B1000) into D1,
and put =STDEVP(B1:B1000) into D2. You might want to use STDEV is
B1:B1000 represent a random sample.

Decide how many bars you want in the histogram. For a normal
distribution, I like to use an odd number, and I find that 11 bins or
more usually works will for a larger amount of data. Suppose the bin
limits are in D7:D17, as above.

Decide what the bin limits should be. For a normal distribution, I
like to use avg+3*sd in D17 with decrements such that D6 would be
avg-3*sd if we had such a bin. To do that, put =6*D2/11 into D5, put
=D1+3*D2 into D17, and put =D17-$D$5 into D16 and copy up through D7.

Assume you entered the FREQUENCY array formula above into E7:E17. Put
=SUM(E7:E17) into E18.

Note that the bin in D17 contains a value, but we are __not__ using it
in the FREQUENCY array formula above. This allows for the case where
the maximum of the data in B1:B1000 exceeds avg+3*sd. There are
alternative constructions.

Now we need to construct the frequency table for the normal
distribution. Create the following formulas:

F7: =NORMDIST(D7,$D$1,$D$2,1)*$E$18
F8: =(NORMDIST(D8,$D$1,$D$2,1)-NORMDIST(D7,$D$1,$D$2,1))*$E$18
copy F8 through F16
F17: =(1-NORMDIST(D16,$D$1,$D$2,1))*$E$18

I don't know how you are making your chart. Perhaps it will suffice
to say that E7:E17 is the source of data for the histogram, F7:F17 is
the source of data for the normal distribution, and D7:D17 are the X-
axis labels for both.

If you need more details on how to make a mix of histogram and line
graph using the Chart Wizard, post a response asking for details.
 
Back
Top