PDA

View Full Version : How to interpolate values between the Troughs/valleys in series



Nick_London
12-13-2007, 05:36 AM
Hi,

I have a timeseries in excel consiting of monthly values that I have charted. I would like to plot a curve connecting the lowest points/troughs/valleys in the chart. I have a formula to work out what the the lowest points in the series are, so I know when the trough occurs and the values they take on:

=IF(AND(B5<B4,B5<B6),B5,NA()) (Column C in spreadsheet)


So assuming my data starts in B2, the formula above identifies and returns the value of the series if it represents a trough in the series and #N/A if it is not a trough.

i.e the formula returns the following for the first 9 observations:

#N/A
#N/A
242547923
#N/A
#N/A
209676087
#N/A
#N/A
255805308


I now what to interpolate the values (#N/A) between each trough sucessive so that the new series tracks the lowest points in the series overtime. Getting the interploated curve is relatively strightfoward as when you chart the data, excel automaticly interpolates and fits a curve to it (via returning #N/A in the cell).

For example in the attachement - in the chart Excel Interpolation - Red Line - the blue line is the raw series (col B) and the red line the interpolated series based on column C.

However I actually need to know what values would generate such a curve - i.e what values each #N/A represents.

Does anyone know if there's a way to obtain the values for these populated cells (other than for the trougths which I already have) or if there is as alternative approach I could use for getting what I want?

Basiclly my end objective is to generate a similar curve to the grey line in chart two which I have drawn in manually so that I have a series that follows the trend in the orginal series but that tracks only the lowest major points in the data. This for me would be the ideal curve based on that raw data but I need to get the underlying values that would generate that curve!

Hope someone can help.

Thanks,

Nick

Nick_London
12-13-2007, 05:38 AM
Sorry - I forget the attachment.

Hi,

I have a timeseries in excel consiting of monthly values that I have charted. I would like to plot a curve connecting the lowest points/troughs/valleys in the chart. I have a formula to work out what the the lowest points in the series are, so I know when the trough occurs and the values they take on:

=IF(AND(B5<B4,B5<B6),B5,NA()) (Column C in spreadsheet)


So assuming my data starts in B2, the formula above identifies and returns the value of the series if it represents a trough in the series and #N/A if it is not a trough.

i.e the formula returns the following for the first 9 observations:

#N/A
#N/A
242547923
#N/A
#N/A
209676087
#N/A
#N/A
255805308


I now what to interpolate the values (#N/A) between each trough sucessive so that the new series tracks the lowest points in the series overtime. Getting the interploated curve is relatively strightfoward as when you chart the data, excel automaticly interpolates and fits a curve to it (via returning #N/A in the cell).

For example in the attachement - in the chart Excel Interpolation - Red Line - the blue line is the raw series (col B) and the red line the interpolated series based on column C.

However I actually need to know what values would generate such a curve - i.e what values each #N/A represents.

Does anyone know if there's a way to obtain the values for these populated cells (other than for the trougths which I already have) or if there is as alternative approach I could use for getting what I want?

Basiclly my end objective is to generate a similar curve to the grey line in chart two which I have drawn in manually so that I have a series that follows the trend in the orginal series but that tracks only the lowest major points in the data. This for me would be the ideal curve based on that raw data but I need to get the underlying values that would generate that curve!

Hope someone can help.

Thanks,

Nick

Bob Phillips
12-13-2007, 06:48 AM
How about a simple smoothing?

D3: blank
E3: blank
F3: C3

D4: =MAX(IF(NOT(ISNA($C$3:C3)),ROW($C$3:C3)))
E4: =MIN(68,IF(NOT(ISNA($C4:$C$68)),ROW($C4:$C$68)))
F4: =IF(ISNA(C3),F2+(INDEX($C$1:$C$68,$E3)-INDEX($C$1:$C$68,$D3))/($E3-$D3-1),C3)

D and E are array formulae. Then just copy D4:F4 down

Andy Pope
12-13-2007, 07:36 AM
This plots the interpolate line pretty closely. It does not match exactly because your line has the smooth option applied.
It also does not match you second chart as that line misses out in between relavent points.

The value for each date is dervied by working out a percentage change between 2 known data points.
The formula is pretty hard coded but could be coded quite easily.

Nick_London
12-17-2007, 08:52 AM
Gents,

Thank you for your suggestions. I think Andy's approach gives a much better type of fit (in term of a smooth curve to the data than Bob's. I guess I can then just take a moving average to smooth out the data a bit more to get a curve.

I have other data series to fit as well. Andy, you said it's would be easy to code. Would I need a macro I could I just do it in a sheet?

Thanks,

Nick

Andy Pope
12-17-2007, 09:33 AM
This VBA code will output the values in column J of the example workbook I posted. Place code in a standard code module.


Sub CalcInterpolatedValues()

Dim lngOutputCol As Long
Dim lngFirstPointRow As Long
Dim lngSecondPointRow As Long
Dim lngRow As Long
Dim rngCell As Range
Dim blnFirstPoint As Boolean
Dim shtData As Worksheet
Dim dblValue As Double
Dim dblGapValue As Double
Dim lngCol As Long
Dim lngItem As Long

lngCol = 3
lngOutputCol = 10
blnFirstPoint = True
Set shtData = Worksheets("Test")

For Each rngCell In shtData.Range(shtData.Range("C4"), shtData.Range("C4").End(xlDown)).Cells
If IsNumeric(rngCell.Value) Then
If blnFirstPoint Then
lngFirstPointRow = rngCell.Row
blnFirstPoint = False
Else
dblGapValue = (shtData.Cells(rngCell.Row, lngCol) - shtData.Cells(lngFirstPointRow, lngCol)) / _
(rngCell.Row - lngFirstPointRow)
lngItem = 1
shtData.Cells(lngFirstPointRow, lngOutputCol) = shtData.Cells(lngFirstPointRow, lngCol)
For lngRow = lngFirstPointRow + 1 To rngCell.Row - 1
dblValue = shtData.Cells(lngFirstPointRow, lngCol)
shtData.Cells(lngRow, lngOutputCol) = dblValue + (dblGapValue * lngItem)
lngItem = lngItem + 1
Next
shtData.Cells(rngCell.Row, lngOutputCol) = shtData.Cells(rngCell.Row, lngCol)

lngFirstPointRow = rngCell.Row
End If
End If
Next

End Sub


It should be possible with formula. As I said the hard part is identify the stop start points within your data.