Conditional Function Application

[Russ.Dilley]

I have a group of about (x,y) data points and a series of
interpolating functions that apply to particular subsets of this data.
For example, I have 'Function 1' that applies for x = 0 to 3.78,
'Function 2' that applies from x = 3.78 to 47.21, etc.

I want to use these interpolating functions to evaluate a given 'x'
value but I want the sheet to be able to recognize that, if I input a
value x = 27.76, it falls between 3.78 and 47.21 and therefore it
should use 'Function 2' to calculate the corresponding 'y' value.

I have about 20 interpolating functions and want to avoid having cells
with a huge series of 'IF' statements and was hoping someone could
point me in the direction of an alternative method.

Thanks,
Russ D.

 

[Ken Johnson]

Hi Russ,
you could use 20 (or so) columns, 1 for each function with an IF so
that x values outside its working domain result in a blank cell ("")
while x values within its working domain result in the interpolation
function value. Then in the column after the 20 or so function columns
you could use the MAX formula to assemble the calculated values into
that column.

Ken Johnson

 

[Russ.Dilley]

Thanks! That worked great! It was a bit tedious to build 20 columns of
polynomials but it is MUCH better than endlessly nested IF statements.

I appreciate the help.

Russ D.

 

[Ken Johnson]

Hi Russ,
Thanks for the feedback. Nice to hear you've had success. By the way,
endless nesting of IFs is not possible, there is a limit of 7 when
nesting. Also, you can hide all those columns to improve appearance.
Ken Johnson