Extrapolating data from a moving average trend curve

[Loren1111]

Hi - I added a moving average trend line (actually, it is a curve) to a histogram scatter chart. As the x-values increase, the y-values start at 0, increase to a maximum value, and then return to 0. From this trend line, I would like to extrapolate the two X values for a given Y value (that is, the x-values for the ascending and descending portions of the curve for a given y value). Is there a way to get XCEL to calculate this? Thanks, Loren

[FlameRetired]

Hello Loren1111. Welcome to the forum.

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

Instructions are in the gold banner at the top of this page.

[MrShorty]

I'm optimistic that we can get Excel to calculate most things. The challenge that I see with interpolating/extrapolating a moving average is that I don't know how to extrapolate a moving average, so I have no way to tell Excel how to extrapolate a moving average. I know how to extrapolate other trendline types, so I can tell Excel how to extrapolate those, but moving average doesn't lend itself well to interpolation/extrapolation.

If you can explain to us how you would perform this interpolation/extrapolation outside of Excel, we should be able to help you program those steps in the spreadsheet.

If you are uncertain how to interpolate/extrapolate a moving average curve, then I would suggest a different regression/trendline type. What you describe (y is ascending then descending) sounds to me like a quadratic type of curve or maybe a "bell" curve (normal or other statistical distribution). If you know what kind of trendline ought to fit your data, then we can program the spreadsheet to calculate the parameters for that trendline, then use the trendline with our calculated parameters to interpolate/extrapolate y at any given x.

[Loren1111]

Hi -thanks for the quick response, I uploaded an example. The data set has two series, which when plotted out gives a sort-of bell shaped curve (labeled scatter plot). The second graph shows the moving average trend line, and the third graph shows the trend line with the data points hidden. What I would like to be able to do is have Excel calculate the two X-axis values corresponding to a given Y-value. For example, by eye the a Y-value of 5 would give x-values of about 50 and about 125 for the ascending and descending portions of the curve. Doe this make sense? Thanks, Loren1111

[MrShorty]

I see two different ways to do this -- an interpolation algorithm or a regression algorithm (I don't know that I would even use moving average as part of the process).

If you are not limited to Excel, Gnumeric and it's build in INTERPOLATION() function would make short work of this.

If you are limited to Excel, here's my strategy for setting up interpolation problems in Excel: https://www.excelforum.com/excel-cha...ml#post3904113

If you are aware of what kind of function this is supposed to be, we can use Excel's regression abilities (LINEST() for "linear" functions and Solver for non-linear regressions) to obtain parameters for a regression equation.

I could even go so far as to regress a simple quadratic or cubic to a selection of points "near" the peak, then use that empirical equation to find x at different values for y.

I can't be sure what approach you would like to move forward with. Let us know how you want to move forward, and we'll help you as best we can.

[Loren1111]

Hi MrShorty - thanks for the comments and the help. I am performing multiple simultaneous analyses on a rather large number (>5,000) of data sets which have been set up in EXCEL, so for the overall project the preference would be have this new component also be in EXCEL. I am not at all a math person, so my terminology is likely to be off a bit (ok, probably quite a bit off!). I have attached a JPG image of what I am looking for. The measurement that I would like to calculate is the width between the midpoints (that is 1/2 of the maximal Y-axis value)of the ascending and descending portions of the curve (black line with double arrowheads). The maximal Y-value and the curve shape/midpoint widths would vary from data-set to data set. I am hoping to have some flexibility as to the y-value to use for the measurements (for example, 30% of the maximal Y value, 50% as mentioned before) or 70%. I only used the moving average function as way to smooth out the curve, but in reality the curve is quite smooth plotting the raw values (which can be seen in the scatter plot in the workbook that I posted). Since EXCEL can plot a "scatter with smooth lines" chart with the raw data or a "moving average trend line", it seems that the numbers that I want to get are already there, I just cannot figure out how to access them. Loren1111

PS - I see that the JPG will not upload - I posted and ECEL workbook with the JPG example embedded in it....

PSS - it doesn't seem to like the workbook with the embedded file

[MrShorty]

I am not at all a math person,...

I'm going to guess that this means you have no idea what kind of statistical distribution might fit these curves, in which case, I would probably use an interpolation approach (but technically that's not a decision I can make for you).

Did you review the link I provided explaining how I would do linear interpolation? Do you have questions about setting something like that up? At this point, I would say that this is the easiest approach to solving the problem you have.

You mention the "smoothed" lines option in the chart. Those are calculated using splines. One can use splines for interpolation, but they are rather difficult to program in Excel. You will either want to search for someone else's pre-programmed UDF to import into Excel, or export this data into Gnumeric and use Gnumeric's built in INTERPOLATION() function that has an option for spline interpolation. I would go so far as to say that, if you want spline interpolation, it will be easier to open your sample file (without charts) in Gnumeric (Gnumeric usually has little difficulty opening simple Excel files), become familiar with the new spreadsheet environment, then do the interpolation using their INTERPOLATION() function than to figure out how to do spline interpolation in Excel.

Again, I can't make programming decisions for you. In order of "easy," the options I see are:

1) Linear interpolation in Excel.
2) Linear or spline interpolation in Gnumeric.
3) other approaches in Excel.

How would you like to proceed? What questions do you have about your chosen approach?

[Loren1111]

Hi again MrShorty - yes, I had looked at the link you provided, thanks. I had already used the FORECAST function in EXCEL to interpolate the "X at 50% of peak y" values in my curve; that is pretty straightforward. In looking at the data sets, I always have a lot more data points on the descending portion of the curve, and it is always pretty linear at the 50% point. So at the end of the day just focusing on that portion of the curve will probably give the most useful data. I had thought that using the ascending and descending curves together would provide sort of an internal control for sample to sample variation, but the smaller number of data points on the ascending portion of the curve kind of nixes that in retrospect (focusing on the trend line rather than the data points misled me a bit). So the only task for me to incorporate this new analysis in my workbook is to figure out how to automate finding the 50% y value midpoint (which is fairly easy) and then to automate the selection of the descending portion of the curve to use for the forecast function (which will be a bit trickier, but should not be too hard). If I have trouble with that, I will be back here asking dumb questions again . I do appreciate your input, it made me think down a couple of different lines and at the end of the day this should work our for me. Thanks again, Loren