Predicting values for non-linear set of data

**scoffman** · 04-21-2011, 08:30 AM

I am working on interpolating some numbers. I have a column of names, 1 column of an incomplete set of numbers, and a 2nd column of complete numbers. I need to predict values for the incomplete column of numbers using the surrounding non-linear set of data.

For instance, the "Column 1" value for "Test 4" needs to be predicted based on B2:B8 and C2:C8. (see file)

Setting up the Forecast() function for every interval works, but is very time consuming. I need a formula that will be able to find the correct interval. This data is not linear, so I need it to be using the correct intervals.

Please see attachment for more information

Any help would be greatly appreciated! Thanks!

**ChemistB** · 04-21-2011, 08:43 AM

Do you have specific blocks of data that you want separated? From your example, your first block is x from 0 to 28.6.

**scoffman** · 04-21-2011, 08:59 AM

Yes, the empty cells need values calculated based on the surrounding values in "Column 1" with their corresponding values in "Column 2"

For instance, values in B3:B7 need to be calculated using 0 and 28.6 from "Column 1" and 1133909 and 2599644 from "Column 2"

Another example, values in B10:B19 need to be calculated using 28.6 and 33.5 from "Column 1" and 2731462 and 6545742 from "Column 2"

These are easy to define using Forecast() and setting it up for each interval. I would like a formula that looks at "Column 2" and decides which interval that value is in based on "Column 1", then uses the 2 values from "Column 1" along with the values in "Column 2" for that interval and calculates the missing values for "Column 1"

Hopefully that wasn't terribly confusing.

**ChemistB** · 04-21-2011, 09:58 AM

Okay, here's how I did it. First, I created a table just for your known values/standards by filtering on non-blanks in col B and copying and pasting that into F29.

Here is the formula that I put into C3 and then copied and pasted into all your blank spaces.

=FORECAST(C3,(INDEX($G$29:$G$35,MATCH(C3,$H$29:$H$35,1)):INDEX($G$29:$G$35,MATCH(TRUE,INDEX(($H$29:$H$35>=C3),0),0))),(INDEX($H$29:$H$35,MATCH(C3,$H$29:$H$35,1)):INDEX($H$29:$H$35,MATCH(TRUE,INDEX(($H$29:$H$35>=C3),0),0))))

It's a little complex but it works.

Maybe someone else can simplify. Some explainations;

INDEX($G$29:$G$35,MATCH(C3,$H$29:$H$35,1)) pulls back the location of the last y value less than the value in C3

INDEX($G$29:$G$35,MATCH(TRUE,INDEX(($H$29:$H$35>=C3),0),0)) pulls back the first y value greater than the value in C3.
This is repeated for the x values and placed inside the forcast function.