# Nested IF Statements

#### gleaphart

##### New Member
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.

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

bump

bump

bump

Replies
14
Views
583
Replies
4
Views
436
Replies
2
Views
828
Replies
1
Views
699
Replies
3
Views
1K

1,190,614
Messages
5,981,944
Members
439,745
Latest member
thepasmiths

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back