Forecast by using Index/Match formulas

[paddyfeldi]

Hi,

I am searching for a formula that forecasts a specific value. Therefore, I would use the Forecast function. The problem is, that I do not know exactly the known y's and x's because the known x value (the first step in the forecast formula) should be dynamic. To find the y's and x's (the second and third step in the formula) I thought about using a Index/Match (To do a lookup) conbination. I always get a #N/A Error. Any ideas how to solve this problem or to rearange the formula?

Please find attached a Excel-example sheet. The formula is in B5. I had to arrange the values descendant and ascendant, since the match function requires this constraints for the match-type -1/1.

Any help would be much appreciated.

Kind regards,
Patrick

[icestationzbra]

i am just going by the definition of the FORECAST function (and not speaking from a position of expertise):

with the following formula in B2,

=FORECAST(A2,$B$13:$B$17,$A$13:$A$17)

does it work for you?

[paddyfeldi]

Hi,

Thank you for your help. Well, In my opinion the formula would work out, if i had to do a linear forecast. The Points I need to forecast are highly nonlinear and therefore, It's not possible to just use the whole bunch of known values.

regards,

Patrick

[icestationzbra]

in the formula that you have entered in B5, what is it that you envision it to do for you? how do you intend to derive the values of known Y's and X's?