Getting rid of text in a data set and replacing it with an average ofthe numbers either side of it.

[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

 

[Ron Rosenfeld]

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

Simpler:

Edit/Replace

Find what: ND
replace with: =NA()

This will replace ND with #N/A.

The #N/A will be ignored by the graphing routine, and a line will connect the
point before with the point after.
--ron

 

[julian.mccrann]

Simpler:

Edit/Replace

Find what:      ND
replace with:   =NA()

This will replace ND with #N/A.

The #N/A will be ignored by the graphing routine, and a line will connect the
point before with the point after.
--ron- Hide quoted text -

- Show quoted text -

Haha, cheers Ron - I will remember that, finally figured out what I
was after was
=IF(B4="No Data", AVERAGE(B3,B5),B4)

The always helpful If Formula, which I really should use more often,
maybe I will now - but I am in debt to your help as well.