Replacing missing values?

I have this fairly large dataset with two kinds of missing values (NA):

1) Holes should be filled with the first before the sequence of
missing values (i e a constant level up to next value)

From: ...30 31 29 NA NA NA 31 32 NA 34
to: ...30 31 29 29 29 29 31 32 32 34

2) Holes shold be filled by a linear interpolation based on the values
between holes.

From: ...30 31 29 NA NA NA 31 32 NA 34
to: ...30 31 29 29.5 30.0 30.5 31 32 33 34

Any chance this could be achieved by macros? Any ideas are most welcome.

Robert
********************
Robert Lundqvist
Dept of mathematics
Lulea University of Technology
Sweden

http://www.ltu.se/web/pub/jsp/polopo...d=4171&a=10697

Hi Robert,

This is the first macro:

Sub replmiss1(oldrng As Range, newrng As Range)
seriestart = True
For Each cella In oldrng
If cella.Value = "NA" Then
If seriestart Then replvalue = oldrng(1, cella.Column - 1)
newrng(1, cella.Column).Value = replvalue
seriestart = False
Else
newrng(1, cella.Column).Value = cella.Value
seriestart = True
End If
Next cella
End Sub


Usage:
Call replmiss1(Range("1:1"), Range("2:2"))

I'm still working on the other!

Regards,
Stefi

„Robert Lundqvist” ezt Ã*rta:

> I have this fairly large dataset with two kinds of missing values (NA):
>
> 1) Holes should be filled with the first before the sequence of
> missing values (i e a constant level up to next value)
>
> From: ...30 31 29 NA NA NA 31 32 NA 34
> to: ...30 31 29 29 29 29 31 32 32 34
>
> 2) Holes shold be filled by a linear interpolation based on the values
> between holes.
>
> From: ...30 31 29 NA NA NA 31 32 NA 34
> to: ...30 31 29 29.5 30.0 30.5 31 32 33 34
>
> Any chance this could be achieved by macros? Any ideas are most welcome.
>
> Robert
> ********************
> Robert Lundqvist
> Dept of mathematics
> Lulea University of Technology
> Sweden
>
> http://www.ltu.se/web/pub/jsp/polopo...d=4171&a=10697
>
>

Hi Robert,

This is the 2nd macro:

Sub replmiss2(oldrng As Range, newrng As Range)
seriestart = True
replvalue1 = 0
replcol1 = 0
For Each cella In oldrng
If cella.Value = "NA" Then
If seriestart Then
replvalue1 = oldrng(1, cella.Column - 1)
replcol1 = cella.Column - 1
End If
seriestart = False
Else
replvalue2 = cella.Value
replcol2 = cella.Column
newrng(1, cella.Column).Value = cella.Value
seriestart = True
If replcol1 > 0 Then
replstep = (replvalue2 - replvalue1) / (replcol2 - replcol1)
For repl = replcol1 + 1 To replcol2 - 1
newrng(1, repl).Value = newrng(1, repl - 1).Value +
replstep
Next repl
replcol1 = 0
End If
End If
Next cella
End Sub


Usage:

Call replmiss2(Range("1:1"), Range("2:2"))

Regards,
Stefi

„Robert Lundqvist” ezt Ã*rta:

> I have this fairly large dataset with two kinds of missing values (NA):
>
> 1) Holes should be filled with the first before the sequence of
> missing values (i e a constant level up to next value)
>
> From: ...30 31 29 NA NA NA 31 32 NA 34
> to: ...30 31 29 29 29 29 31 32 32 34
>
> 2) Holes shold be filled by a linear interpolation based on the values
> between holes.
>
> From: ...30 31 29 NA NA NA 31 32 NA 34
> to: ...30 31 29 29.5 30.0 30.5 31 32 33 34
>
> Any chance this could be achieved by macros? Any ideas are most welcome.
>
> Robert
> ********************
> Robert Lundqvist
> Dept of mathematics
> Lulea University of Technology
> Sweden
>
> http://www.ltu.se/web/pub/jsp/polopo...d=4171&a=10697
>
>