+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    06-22-2013
    Location
    brisbane, australia
    MS-Off Ver
    Excel 2011
    Posts
    9

    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).
    Attached Files Attached Files
    Last edited by tomstheword; 08-18-2013 at 04:11 AM. Reason: Found an error in the example. Sorted now.

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    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
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    06-22-2013
    Location
    brisbane, australia
    MS-Off Ver
    Excel 2011
    Posts
    9

    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. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    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))
    Last edited by martindwilson; 08-18-2013 at 06:31 AM.

  5. #5
    Registered User
    Join Date
    06-22-2013
    Location
    brisbane, australia
    MS-Off Ver
    Excel 2011
    Posts
    9

    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))))
    Last edited by tomstheword; 08-18-2013 at 07:18 AM. Reason: a solution

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Conditional Format between two values based on another value
    By purdue7997 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-19-2013, 03:57 AM
  2. [SOLVED] Conditional format based on ISTEXT and ISBLANK values
    By JDobbsy1987 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-29-2012, 06:48 PM
  3. [SOLVED] Conditional format for duplicate values based on criteria in a different cell
    By excelheaven in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-04-2012, 11:34 AM
  4. Replies: 10
    Last Post: 10-18-2010, 06:42 AM
  5. [SOLVED] Conditional Format based on date ranges
    By Corey in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-31-2006, 12:40 AM

Bookmarks

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