Interpolation - quadratic

Hi,

I am trying to interpolate AGDD/x value for a given NDVI/y value using formula taken from the following thread on this forum

http://www.excelforum.com/excel-gene...rpolation.html.

My NDVI/y as a function of AGDD/x data has a quadratic shape so y is not (should not be) sorted ascending while the formula I think demand that order), but x is in an ascending order. In many of my spreadsheets, the result returned valid, but not always. I have uploaded a sample spreadsheet with this post. I will use this formula across sheets (I can do that), but as a second question, I need to return the results across sheets in to a new sheet by sheet name. Any help?

Sorry I don't have knowledge of programing.

Thanks,
Abys

What's x? What's y? What are you trying to interpolate?

shg,

x is AGDD (accumulated growing degree day) and y is NDVI (normalized difference vegetation index).
I need to interpolate AGDD/x for a given NDVI/y value. Note that since the function is quadratic, each NDVI/y value will have two possible AGDD/x values. I need the first AGDD/x value.

Thanks

From the help file for the LOOKUP() function (http://office.microsoft.com/en-us/ex...947.aspx?CTT=1) "IMPORTANT The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value." If you there is no way to structure the problem so NDVI/x is sorted in ascending order, then I think the LOOKUP() function will not work for you.

A clarification: when you give it a NDVI value of 0.614, how did you determine that it should interpolate based on rows 20 and 21? The interval in rows 20 and 21 contain 0.614, but so does the interval between rows 22 and 23. How did you know to choose the first one and not the second?

I plotted column E against column B in a scatter plot. How do you know when to interpolate along the curve from row 14 to 21, and how do you know when to interpolate along the curve from row 22 to end? I note that, if I change the references in the "incorrect" formulas to only use rows 13 to 22, the results agree with the "correct" results you list. Perhaps you need to consider how you determine which section of the curve you need to interpolate, then have separate LOOKUP()/interpolate functions for each "section" of the overall data set.

MrShorty,

Thanks for the detail response.
Yes you are right that every NDVI/y (my lookup vector) has two possible AGDD/x values. Also, I should not sorted this data (NDVI/y) in to ascending order.
Yes, considering the data to half of the quadratic curve returned a valid result as you test it. But the problem is that I have lots of other worksheets to be done across with a single formula, that have a possible different NDVI/y peaks in time making it difficult to cut the data in to half.

As you suggested, finding another function may be a way to consider.

Thanks

I think you need to understand and explain conceptually what you're trying to do.

But the problem is I have lost of other worksheets to be done across with a single formula, that have a possible different NDVI/y peaks in time making it difficult to cut the data in to half.

Maybe the first step in developing this algorithm, then, is to first figure out how you will locate the "peak" or apex of the "quadratic" curve? Perhaps a column calculating dy/dx and locating where the slope changes sign. Or a MATCH(MAX(NDVI),...) combination that will tell you where to cut the data in half. Once you know where the apex of the curve is, then you can structure the interpolation algorithm to perform the interpolation on the desired half of the curve.

I realize this will probably not end up being a "single function" (or maybe I'm just not good enough to nest all the necessary functions into a single function). My first thought would be to try the MATCH(MAX(NDVI),NDVI,0) combination and see if that much works ok.