Frequencies and histograms

[adeline.morris]

Hi there,

I am trying to use Excel to generate a histogram for the ages of managers from Urban Child Care Centres. I have 50 different ages and I want the bin cut-off points to be 30,40,50,60,70.

When I try to generate frequencies using the frequency function, or to generate a histogram using the Analysis Toolpak, the frequencies that Excel calculates (and the cumulative frequencies) are different to the ones I have calculated and know to be correct!

What might I have done wrong? I specify the bins array by highlighting a column containing the values 30,40,50,60,70.

Thanks,

Adeline

 

[Andrew Poulsom]

Welcome to the Board.

Can you post some sample data, the frequency formula and its results and the results you would expect?

 

[adeline.morris]

Yes, of course.

The values are:
<table x:str="" style="border-collapse: collapse; width: 48pt;" width="64" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" x:num="" width="64" align="right" height="17">42</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">30</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">53</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">50</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">52</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">30</td> </tr> <tr style="height: 13.5pt;" height="18"> <td style="height: 13.5pt;" x:num="" align="right" height="18">55</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">49</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">61</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">74</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">26</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">58</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">40</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">40</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">28</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">36</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">30</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">33</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">31</td> </tr> <tr style="height: 13.5pt;" height="18"> <td style="height: 13.5pt;" x:num="" align="right" height="18">37</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">32</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">37</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">30</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">32</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">23</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">32</td> </tr> <tr style="height: 13.5pt;" height="18"> <td style="height: 13.5pt;" x:num="" align="right" height="18">58</td> </tr> <tr style="height: 13.5pt;" height="18"> <td style="height: 13.5pt;" x:num="" align="right" height="18">43</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">30</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">29</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">34</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">50</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">47</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">31</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">35</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">26</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">64</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">46</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">40</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">43</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">57</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">30</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">49</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">40</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">25</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">50</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">52</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">32</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">60</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">54</td> </tr> </tbody></table>

The frequencies I would expect are:

20-29:6
30-39:18
40-49:11
50-59: 11
60-69: 3
70-79: 1

The frequency table that I generate using the frequency function comes out as:
<table x:str="" style="border-collapse: collapse; width: 96pt;" width="128" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" width="64" height="17">Ages</td> <td class="xl24" style="width: 48pt;" width="64">Frequency</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">30</td> <td x:num="" align="right">12</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">40</td> <td x:num="" align="right">16</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">50</td> <td x:num="" align="right">10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">60</td> <td x:num="" align="right">9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">70</td> <td x:num="" align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">80</td> <td x:num="" align="right">1</td> </tr> </tbody></table>

The histogram is based on the same results for frequencies.

What am I doing wrong?

Thanks ever so much for your interest!

A

 

[adeline.morris]

Apologies, I forgot the frequency formula, which is:=FREQUENCY(A2:A51;B2:B6)

 

[GlennUK]

Change your age buckets to be 29, 39, 49, 59, 69, and 79.

 

[Andrew Poulsom]

Look at the Help on the FREQUENCY function. The bin entries are upper bounds not lower bounds. For your ranges you need bins of 29, 39, 49, 59, 69 and 79.

 

[adeline.morris]

I have actually tried that, to no avail

 

[GlennUK]

I tried it with your data, and it works perfectly. Can you tell us whether you altered your FREQUENCY formula? And if so, did you remember to re-enter it using Ctrl-Shift-Enter ( over the whole range C2 to C6 ).