# Conditional format based on being outside of two ranges of values - forecast

1. ## Conditional format based on being outside of two ranges of values - forecast

Hi,

I am trying to conditionally format a cell based on it being outside of two values that I am obtaining using forecast.

I can achieve what I am after by doing two separate formats but I would like to achieve it with one due to the huge number of these that will be required in the final sheet.

The data is linear between the each individual point but obviously there are two linear lines in each set.

Any ideas would be appreciated.

I have added the formula I am using as text below each cell so you don't have to look in the conditional format section (there is a space at the very front).

2. ## Re: Conditional format based on being outside of two ranges of values - forecast

use and or OR
=and(a1>100,a1<200) for between
=OR(a1>200,a1<100) for outside

3. ## Re: Conditional format based on being outside of two ranges of values - forecast

Thanks for the reply but not what I was after.

Basically I am trying to forecast x if y is anywhere between 1.0 and 3.0

e.g. on the table below 1.8 would equal 10 and 2.5 would equal 12.5

y x

3 15
2 10
1 10

4. ## Re: Conditional format based on being outside of two ranges of values - forecast

sorry don't get that never used forecast dont know how it works or even understand the concept!
cant you just combine them into another or?
=or(or(formula1,formula2),or(formula3,formula4))

5. ## Re: Conditional format based on being outside of two ranges of values - forecast

Yes. It turns out you can. This is what I came up with, its a bit longer than I was hoping for but it is still a big improvement. Thanks for your help.

=OR(OR(B8<(FORECAST(B7,\$F\$8:\$F\$9,\$E\$8:\$E\$9)),B8>(FORECAST(B7,\$I\$8:\$I\$9,\$H\$8:\$H\$9))),OR(B8<(FORECAST(B7,\$F\$9:\$F\$10,\$E\$9:\$E\$10)),B8>(FORECAST(B7,\$I\$9:\$I\$10,\$H\$9:\$H\$10))))

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1