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
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