Histogram Question

[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

 

[joeu2004]

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 ?

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 D717 and you want the frequencies in E7:E17, select
E7:E17, and enter the following array formula [*]:

=FREQUENCY(B1:B1000,D116)

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

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 D717, 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 D717 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.

 

[Bob]

Hello,

Much thanks.
Appreciate all the details.

Regards,
Bob

 

[IDS]

Instead of using the built in histogram feature use Mike Girven's from
ExcelIsFun as, in my opinion, it give a nice looking result. The proof
copy of his book is freely available at
https://flightline.highline.edu/mgirvin/YouTubeExcelIsFun/book.htm.

Histograms
Histograms are a special type of column chart where the horizontal
categories are grouped numbers, such as "0 up to 10", "10 up to 20",
"20 up to 30", etc. and where the gap width between each column is
zero. The "zero gap width" visually indicates that there are no values
possible between categories.

In Figure 1026 we can see an example of when a Histogram is perfect
for visualizing data. The scores for a student test are in column A
and we need to count how many students got a score in the categories:
" From 90 up to (but not including) 100, in math symbols it
would be: 90 >= Score < 100.
" From80 up to (but not including) 90, in math symbols it would
be: 80 >= Score < 90.
" Etc.

For example, we can see:
" 10 students got a score in the category 90 up to 100, which
means those 10 students got a score in the 90s
" 17 students got a score in the category 80 up to 90, which
means those 17 students got a score in the 80s

Note: we saw this same sort of "between" criteria for counting in the
COUNTIFS function, the PivotTable (Frequency Table), and the Advanced
Filter sections of this book.

You can see how useful a chart like this can be. The keys for deciding
when to use a Histogram are:

1) There are grouped number categories with an upper and lower
limit.
2) The categories do not overlap and therefore there are no gaps
between the columns.

Now let's see how to create this chart.
Figure 1026


To follow along, open the file named "Excelisfun-Start.xlsm" and
navigate to the sheet named "C(3)".
?

Figure 1027
1. On the sheet "C(3)" click in cell G3 and create the formula:

=COUNTIFS($A$3:$A$97,">="&C3,$A$3:$A$97,"<"&D3)

2. For reference to how to create this formula and what it means
look back to Figure 312 and Figure 506 and then re-read those
sections.
3. Copy the formula in cell G3 into the range G3:G12.







Figure 1028
4. Select the range F2:G12.
5. Go to Insert tab, then in the Charts group click on the Column
chart dropdown list, then click on the first item called "Clustered
Column".