Trying to make a histogram. Frequency seems to be off which is making the cumulative % off.

[el_juanmarron]

My first post. Hope I make everything clear.

I have to recreate a histogram, that is printed in my textbook, of the rate of return on t-bills from 1926-2009. The professor prepared a spreadsheet with the rates on it, and on the picture in the book the bins range from -90 through 90.

When I try to recreate the histogram my frequency looks exactly like it does in the book, but my cumulative % is way off and I don't know what the problem is. If the cumulative % is off then the frequency must be off right?

I have a photo of the histogram in my book stored on image shack. I'm not sure if I'm supposed to post it on the forum though. If I can just let me know.

The first are the rates of return and the second are the bins, frequency and cumulative %

Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>G</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #DD0806;background-color: #C0C0C0;;">T-Bills</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;;">3.19</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">3.13</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">3.54</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">4.74</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">2.43</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">1.09</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">0.95</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">0.30</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">0.18</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">0.14</td></tr><tr><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">0.18</td></tr><tr><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">0.29</td></tr><tr><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">-0.04</td></tr><tr><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">0.01</td></tr><tr><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">-0.02</td></tr><tr><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">0.04</td></tr><tr><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">0.28</td></tr><tr><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">0.36</td></tr><tr><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">0.33</td></tr><tr><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">0.32</td></tr><tr><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">0.36</td></tr><tr><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">0.50</td></tr><tr><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">0.81</td></tr><tr><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;">1.12</td></tr><tr><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">1.22</td></tr><tr><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;">1.49</td></tr><tr><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">1.65</td></tr><tr><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;">1.83</td></tr><tr><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">0.86</td></tr><tr><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;;">1.57</td></tr><tr><td style="color: #161120;text-align: center;">34</td><td style="text-align: right;;">2.47</td></tr><tr><td style="color: #161120;text-align: center;">35</td><td style="text-align: right;;">3.15</td></tr><tr><td style="color: #161120;text-align: center;">36</td><td style="text-align: right;;">1.53</td></tr><tr><td style="color: #161120;text-align: center;">37</td><td style="text-align: right;;">2.98</td></tr><tr><td style="color: #161120;text-align: center;">38</td><td style="text-align: right;;">2.67</td></tr><tr><td style="color: #161120;text-align: center;">39</td><td style="text-align: right;;">2.12</td></tr><tr><td style="color: #161120;text-align: center;">40</td><td style="text-align: right;;">2.73</td></tr><tr><td style="color: #161120;text-align: center;">41</td><td style="text-align: right;;">3.11</td></tr><tr><td style="color: #161120;text-align: center;">42</td><td style="text-align: right;;">3.53</td></tr><tr><td style="color: #161120;text-align: center;">43</td><td style="text-align: right;;">3.92</td></tr><tr><td style="color: #161120;text-align: center;">44</td><td style="text-align: right;;">4.75</td></tr><tr><td style="color: #161120;text-align: center;">45</td><td style="text-align: right;;">4.21</td></tr><tr><td style="color: #161120;text-align: center;">46</td><td style="text-align: right;;">5.22</td></tr><tr><td style="color: #161120;text-align: center;">47</td><td style="text-align: right;;">6.57</td></tr><tr><td style="color: #161120;text-align: center;">48</td><td style="text-align: right;;">6.52</td></tr><tr><td style="color: #161120;text-align: center;">49</td><td style="text-align: right;;">4.39</td></tr><tr><td style="color: #161120;text-align: center;">50</td><td style="text-align: right;;">3.84</td></tr><tr><td style="color: #161120;text-align: center;">51</td><td style="text-align: right;;">6.93</td></tr><tr><td style="color: #161120;text-align: center;">52</td><td style="text-align: right;;">8.01</td></tr><tr><td style="color: #161120;text-align: center;">53</td><td style="text-align: right;;">5.80</td></tr><tr><td style="color: #161120;text-align: center;">54</td><td style="text-align: right;;">5.08</td></tr><tr><td style="color: #161120;text-align: center;">55</td><td style="text-align: right;;">5.13</td></tr><tr><td style="color: #161120;text-align: center;">56</td><td style="text-align: right;;">7.19</td></tr><tr><td style="color: #161120;text-align: center;">57</td><td style="text-align: right;;">10.38</td></tr><tr><td style="color: #161120;text-align: center;">58</td><td style="text-align: right;;">11.26</td></tr><tr><td style="color: #161120;text-align: center;">59</td><td style="text-align: right;;">14.72</td></tr><tr><td style="color: #161120;text-align: center;">60</td><td style="text-align: right;;">10.53</td></tr><tr><td style="color: #161120;text-align: center;">61</td><td style="text-align: right;;">8.80</td></tr><tr><td style="color: #161120;text-align: center;">62</td><td style="text-align: right;;">9.84</td></tr><tr><td style="color: #161120;text-align: center;">63</td><td style="text-align: right;;">7.72</td></tr><tr><td style="color: #161120;text-align: center;">64</td><td style="text-align: right;;">6.16</td></tr><tr><td style="color: #161120;text-align: center;">65</td><td style="text-align: right;;">5.47</td></tr><tr><td style="color: #161120;text-align: center;">66</td><td style="text-align: right;;">6.36</td></tr><tr><td style="color: #161120;text-align: center;">67</td><td style="text-align: right;;">8.38</td></tr><tr><td style="color: #161120;text-align: center;">68</td><td style="text-align: right;;">7.84</td></tr><tr><td style="color: #161120;text-align: center;">69</td><td style="text-align: right;;">5.60</td></tr><tr><td style="color: #161120;text-align: center;">70</td><td style="text-align: right;;">3.50</td></tr><tr><td style="color: #161120;text-align: center;">71</td><td style="text-align: right;;">2.90</td></tr><tr><td style="color: #161120;text-align: center;">72</td><td style="text-align: right;;">3.91</td></tr><tr><td style="color: #161120;text-align: center;">73</td><td style="text-align: right;;">5.60</td></tr><tr><td style="color: #161120;text-align: center;">74</td><td style="text-align: right;;">5.20</td></tr><tr><td style="color: #161120;text-align: center;">75</td><td style="text-align: right;;">5.25</td></tr><tr><td style="color: #161120;text-align: center;">76</td><td style="text-align: right;;">4.85</td></tr><tr><td style="color: #161120;text-align: center;">77</td><td style="text-align: right;;">4.69</td></tr><tr><td style="color: #161120;text-align: center;">78</td><td style="text-align: right;;">5.88</td></tr><tr><td style="color: #161120;text-align: center;">79</td><td style="text-align: right;;">3.86</td></tr><tr><td style="color: #161120;text-align: center;">80</td><td style="text-align: right;;">1.63</td></tr><tr><td style="color: #161120;text-align: center;">81</td><td style="text-align: right;;">1.02</td></tr><tr><td style="color: #161120;text-align: center;">82</td><td style="text-align: right;;">1.19</td></tr><tr><td style="color: #161120;text-align: center;">83</td><td style="text-align: right;;">2.98</td></tr><tr><td style="color: #161120;text-align: center;">84</td><td style="text-align: right;;">4.81</td></tr><tr><td style="color: #161120;text-align: center;">85</td><td style="text-align: right;;">4.67</td></tr><tr><td style="color: #161120;text-align: center;">86</td><td style="text-align: right;;">1.64</td></tr><tr><td style="color: #161120;text-align: center;">87</td><td style="text-align: right;border-bottom: 1px solid black;;">0.05</td></tr></tbody></table>

Return data 2009​

Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>P</th><th>Q</th><th>R</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;font-style: italic;;">Bin</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;font-style: italic;;">Frequency</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;font-style: italic;;">Cumulative %</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;;">-90</td><td style="text-align: right;border-top: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;;">0.00%</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">-85</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.00%</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">-80</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.00%</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">-75</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.00%</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">-70</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.00%</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">-65</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.00%</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">-60</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.00%</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">-55</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.00%</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">-50</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.00%</td></tr><tr><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">-45</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.00%</td></tr><tr><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">-40</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.00%</td></tr><tr><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">-35</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.00%</td></tr><tr><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">-30</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.00%</td></tr><tr><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">-25</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.00%</td></tr><tr><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">-20</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.00%</td></tr><tr><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">-15</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.00%</td></tr><tr><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">-10</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.00%</td></tr><tr><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">-5</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.00%</td></tr><tr><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2.38%</td></tr><tr><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">5</td><td style="text-align: right;;">56</td><td style="text-align: right;;">69.05%</td></tr><tr><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">10</td><td style="text-align: right;;">22</td><td style="text-align: right;;">95.24%</td></tr><tr><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">15</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100.00%</td></tr><tr><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;">20</td><td style="text-align: right;;">0</td><td style="text-align: right;;">100.00%</td></tr><tr><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">25</td><td style="text-align: right;;">0</td><td style="text-align: right;;">100.00%</td></tr><tr><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;">30</td><td style="text-align: right;;">0</td><td style="text-align: right;;">100.00%</td></tr><tr><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">35</td><td style="text-align: right;;">0</td><td style="text-align: right;;">100.00%</td></tr><tr><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;">40</td><td style="text-align: right;;">0</td><td style="text-align: right;;">100.00%</td></tr><tr><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">45</td><td style="text-align: right;;">0</td><td style="text-align: right;;">100.00%</td></tr><tr><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;;">50</td><td style="text-align: right;;">0</td><td style="text-align: right;;">100.00%</td></tr><tr><td style="color: #161120;text-align: center;">34</td><td style="text-align: right;;">55</td><td style="text-align: right;;">0</td><td style="text-align: right;;">100.00%</td></tr><tr><td style="color: #161120;text-align: center;">35</td><td style="text-align: right;;">60</td><td style="text-align: right;;">0</td><td style="text-align: right;;">100.00%</td></tr><tr><td style="color: #161120;text-align: center;">36</td><td style="text-align: right;;">65</td><td style="text-align: right;;">0</td><td style="text-align: right;;">100.00%</td></tr><tr><td style="color: #161120;text-align: center;">37</td><td style="text-align: right;;">70</td><td style="text-align: right;;">0</td><td style="text-align: right;;">100.00%</td></tr><tr><td style="color: #161120;text-align: center;">38</td><td style="text-align: right;;">75</td><td style="text-align: right;;">0</td><td style="text-align: right;;">100.00%</td></tr><tr><td style="color: #161120;text-align: center;">39</td><td style="text-align: right;;">80</td><td style="text-align: right;;">0</td><td style="text-align: right;;">100.00%</td></tr><tr><td style="color: #161120;text-align: center;">40</td><td style="text-align: right;;">85</td><td style="text-align: right;;">0</td><td style="text-align: right;;">100.00%</td></tr><tr><td style="color: #161120;text-align: center;">41</td><td style="text-align: right;;">90</td><td style="text-align: right;;">0</td><td style="text-align: right;;">100.00%</td></tr><tr><td style="color: #161120;text-align: center;">42</td><td style="border-bottom: 1px solid black;;">More</td><td style="text-align: right;border-bottom: 1px solid black;;">0</td><td style="text-align: right;border-bottom: 1px solid black;;">100.00%</td></tr></tbody></table>

Return data 2009​

 

[p45cal]

It looks fine, the calculations seem to be right; I'm a little surprised that there's such a wide range for the bins.
Post a link to the image (if it doesn't let you post links because you haven't many posts then use the likes of:3ws dot imageshack slash tdf345 dot ... )

Is it cumulative % you want, or just straight %, when the 3 values would be:
<table border="0" cellpadding="0" cellspacing="0" width="64"><col style="width:48pt" width="64"> <tbody><tr style="height:12.75pt" height="17"> <td class="xl23" style="height:12.75pt; width:48pt" align="right" height="17" width="64">2.38%</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl23" style="height:12.75pt" align="right" height="17">66.67%</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl23" style="height:12.75pt" align="right" height="17">26.19%</td> </tr> </tbody></table>

 

[el_juanmarron]

Thank you so much for the quick reply. I'm new to using image shack as well so the picture may be small.

It's the first graph on the image. sorry it's not bigger.

This is how mines looks

 

[p45cal]

Yes, it's not cumulative % you want but straight %. I missed one value off:

Excel Workbook
66.67%
26.19%
4.76%
Sheet

Adding a column S to your sheet, and a sum in cell Q44. Drag/copy formula in S21 up and down:

Excel Workbook
	P	Q	R	S
21	-10	0	0.00%	0.00%
22	-5	0	0.00%	0.00%
23	0	2	2.38%	2.38%
24	5	56	69.05%	66.67%
25	10	22	95.24%	26.19%
26	15	4	100.00%	4.76%
27	20	0	100.00%	0.00%
28	25	0	100.00%	0.00%
29	30	0	100.00%	0.00%
30	35	0	100.00%	0.00%
31	40	0	100.00%	0.00%
32	45	0	100.00%	0.00%
33	50	0	100.00%	0.00%
34	55	0	100.00%	0.00%
35	60	0	100.00%	0.00%
36	65	0	100.00%	0.00%
37	70	0	100.00%	0.00%
38	75	0	100.00%	0.00%
39	80	0	100.00%	0.00%
40	85	0	100.00%	0.00%
41	90	0	100.00%	0.00%
42	More	0	100.00%	0.00%
43
44		84
Sheet

 

[p45cal]

…but you do realise that the line graph in the book is NOT a percent chart, but probably a curve generated using the mean and standard deviation of the data?

 

[el_juanmarron]

When I go to make the histogram what do I select as the ranges? Also I seem to have a second vertical axis for some reason.

I know I'm asking a lot of questions I'm just very new to excel.

edit:
so I'm not supposed to make a histogram? How would I generate a curve using the std and mean?

Now I'm all confused. lol.

 

[p45cal]

Looking more closely at the line chart in the book, the peak of its curve is at an x value to the right of the tallest bar, more than 5, so it can't be a normal distribution curve, as the mean is 3.71. So I don't know what that line is.
What have you been tasked to do?

 

[el_juanmarron]

The assignment was to simply recreate the chart in the book using the values given in the workbook. In the work book the professor has some calculations worked out underneath the rates of returns, The avg of all, avg of 1968-2009, avg of 1926-1967, standard deviation of all, std 1968-2009, and 1926-1967. These are the corresponding values- <b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">88</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #DD0806;background-color: #C0C0C0;;">T-Bills</td></tr><tr ><td style="color: #161120;text-align: center;">89</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">3.71</td></tr><tr ><td style="color: #161120;text-align: center;">90</td><td style="text-align: right;;">5.75</td></tr><tr ><td style="color: #161120;text-align: center;">91</td><td style="text-align: right;;">1.67</td></tr><tr ><td style="color: #161120;text-align: center;">92</td><td style="text-align: right;;">3.09</td></tr><tr ><td style="color: #161120;text-align: center;">93</td><td style="text-align: right;;">2.94</td></tr><tr ><td style="color: #161120;text-align: center;">94</td><td style="text-align: right;border-bottom: 1px solid black;;">1.45</td></tr></tbody></table><p style="width:9.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Return data 2009</p><br /><br />

 

[p45cal]

Still difficult to determine what the line might be - can you add more info?
Is the course meant to to teach you about:
Excel,
Statistics,
or
Money Markets?

 

[el_juanmarron]

It's a portfolio management class and this is the book that the graph came out of- http://highered.mcgraw-hill.com/sites/007338237x/information_center_view0/

I'm looking in the book and the graphs actually doesn't have that line in it. It looks like he added the line himself.

Thank you so much for going through this with me.