J
julian.mccrann
Table is here
http://www.federalreserve.gov/releases/h15/data/Business_day/H15_FF_O.txt
I have taken this table and graphed it. Problem is on public holidays
there is an ND - For Example
07/01/1954, 1.13
07/02/1954, 1.25
07/05/1954, 0.88
07/06/1954, 0.25
07/07/1954, 1.00
07/08/1954, 1.25
07/09/1954, 1.25
07/12/1954, 1.25
07/13/1954, 1.13
07/14/1954, 1.13
07/15/1954, 0.75
07/16/1954, ND
07/19/1954, 0.50
07/20/1954, 0.25
07/21/1954, 0.25
07/22/1954, 0.75
I have split the cells and graphed the results for each day. I also
replaced ND with No Data to make them more obvious when looking at the
spreadsheet.
So I now have (Of Daily Fed Interest Rates 1954-2008)
07/01/1954 1.13
07/02/1954 1.25
07/05/1954 0.88
07/06/1954 0.25
07/07/1954 1
07/08/1954 1.25
07/09/1954 1.25
07/12/1954 1.25
07/13/1954 1.13
07/14/1954 1.13
07/15/1954 0.75
07/16/1954 No Data
07/19/1954 0.5
07/20/1954 0.25
07/21/1954 0.25
07/22/1954 0.75
The problem is, when this is graphed it assigns the value of 0 to
wherever it says No Data. There are 498 instances of No Data through
the results. This obviously totally stuffs up the graph.
I want to smooth out all these days such that I can replace all the No
Datas with this simple formula
=Average (previous cell (above), next cell (below))
Just to basically cut the middle on all these cells so they don't
stuff up the graph line.
There are also the added problems of repeated No Data days like this
11/23/1954 1.44
11/24/1954 1.44
11/25/1954 No Data
11/26/1954 No Data
11/29/1954 1.44
11/30/1954 1.38
Obviously the average for both of those "No Datas" should be 1.44 -
but how do you make this simple formula apply to all the No Data cells
and get around that potential circularity?
First off - how do I create a formula that will find all instances of
No Data and replace them with a formula?
Second - how do I avoid circularity problems when there are a couple
of No Data cells one after the other?
Third - Perhaps another way to do it is to just blank all these cells
out of the graph, but how do I blank out all the rows with No Data in
them with a formula/macro?
Finally, I will probably work this out soon enough, but I thought I
could post it here and see if anyone can solve this before I work it
out.
Cheers
http://www.federalreserve.gov/releases/h15/data/Business_day/H15_FF_O.txt
I have taken this table and graphed it. Problem is on public holidays
there is an ND - For Example
07/01/1954, 1.13
07/02/1954, 1.25
07/05/1954, 0.88
07/06/1954, 0.25
07/07/1954, 1.00
07/08/1954, 1.25
07/09/1954, 1.25
07/12/1954, 1.25
07/13/1954, 1.13
07/14/1954, 1.13
07/15/1954, 0.75
07/16/1954, ND
07/19/1954, 0.50
07/20/1954, 0.25
07/21/1954, 0.25
07/22/1954, 0.75
I have split the cells and graphed the results for each day. I also
replaced ND with No Data to make them more obvious when looking at the
spreadsheet.
So I now have (Of Daily Fed Interest Rates 1954-2008)
07/01/1954 1.13
07/02/1954 1.25
07/05/1954 0.88
07/06/1954 0.25
07/07/1954 1
07/08/1954 1.25
07/09/1954 1.25
07/12/1954 1.25
07/13/1954 1.13
07/14/1954 1.13
07/15/1954 0.75
07/16/1954 No Data
07/19/1954 0.5
07/20/1954 0.25
07/21/1954 0.25
07/22/1954 0.75
The problem is, when this is graphed it assigns the value of 0 to
wherever it says No Data. There are 498 instances of No Data through
the results. This obviously totally stuffs up the graph.
I want to smooth out all these days such that I can replace all the No
Datas with this simple formula
=Average (previous cell (above), next cell (below))
Just to basically cut the middle on all these cells so they don't
stuff up the graph line.
There are also the added problems of repeated No Data days like this
11/23/1954 1.44
11/24/1954 1.44
11/25/1954 No Data
11/26/1954 No Data
11/29/1954 1.44
11/30/1954 1.38
Obviously the average for both of those "No Datas" should be 1.44 -
but how do you make this simple formula apply to all the No Data cells
and get around that potential circularity?
First off - how do I create a formula that will find all instances of
No Data and replace them with a formula?
Second - how do I avoid circularity problems when there are a couple
of No Data cells one after the other?
Third - Perhaps another way to do it is to just blank all these cells
out of the graph, but how do I blank out all the rows with No Data in
them with a formula/macro?
Finally, I will probably work this out soon enough, but I thought I
could post it here and see if anyone can solve this before I work it
out.
Cheers