Nested IF Statements

[gleaphart]

Hello,

Question on setting up a formula that I believe will use IF, AND, OR, and FORECAST functions in order to interpolate. I have a spreadsheet that when I input a value I receive an output value only if the input value falls with in a certain range.

Example:

I have 4 ranges, 0 to 0.002, 0.002 to 0.01, 0.01 to 0.02, and 0.02 to 0.05. Think of these as the known x values in the FORECAST function. I have corresponding calculated values as the y values in the FORECAST function (y2 and y1 as seen below)

When I enter an input value, say 0.0019, that falls in the first range, 0 to 0.002, I receive the desired numerical output. This formula is as follows:

=IF(AND(INPUT > 0, INPUT <= 0.002), FORECAST(INPUT, y1:y2, 0:0.002)

Now, I want to be able to put in an input value and have the formula recognize what range it is in and produce the desired numerical output. The formula I have is getting hung up because when I put in a value that is in one of the 4 ranges but not in the other 3 ranges the output is false. What formula do I need that will recognize the range, interpolate in that range correctly, and be able to ignore that the input value does not fall in the other 3 ranges.

Here is what I have but like I said it does not work (I'm pretty sure I'll have to use OR statements but I'm not sure how to put the whole thing together)

=IF(AND(INPUT > 0, INPUT <= 0.002), FORECAST(INPUT, y1:y2, 0:0.002), IF(AND(INPUT > 0.002, INPUT <= 0.01), FORECAST(INPUT, y1:y2, 0.002:0.01), IF(AND(INPUT > 0.01, INPUT <= 0.02), FORECAST(INPUT, y1:y2, 0.01:0.02), IF(AND(INPUT > 0.02, INPUT <= 0.05), FORECAST(INPUT, y1:y2, 0.02:0.05)

I appreciate the help.

 

[gleaphart]

bump

 

[gleaphart]

bump

 

[gleaphart]

bump