+ Reply to Thread
Results 1 to 4 of 4

Nested If Formula problem

  1. #1
    Registered User
    Join Date
    09-06-2018
    Location
    Raleigh, NC
    MS-Off Ver
    MS365 V2202
    Posts
    24

    Post Nested If Formula problem

    I need to combine 3 different If Formulas into one, but I can't seem to get it to work for some reason. Here are the three formulas:
    =IF(FW80<=CH80,"Adjust to pink","Stay at Forecast")
    OR
    =IF(FW80="","Zero Forecast","Okay")
    OR
    =IF(AND(GD80>=CH80,FW80>=GD80),"Change to Open","Stay at Forecast")

    One of these situations will happen based on the data that I have in the excel doc.

    I'm either getting a value error, or just a general excel formula error.

  2. #2
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,832

    Re: Nested If Formula problem

    You can't nest the IF formulas the way you have them written because the IF statements already handle both a TRUE and FALSE condition. Therefore, it will never continue to the other IF statements. In other words, the first IF statement says if it's true, the value should be "Adjust to pink". If it's FALSE, the the value should be "Stay at Forecast". It stops there because there's no reason to go any further.

    If, however, your logic was something like, "if FW80 <=CH80, then the value should be "Adjust to pink". If it's NOT <=CH80, then check to see if it's blank. If so, the the value should be "Zero Forecast". If it's not blank, then check to see if GD80>=CH80 AND FW80>=GD80, then the value should be "Change to Open". Then you have to decide on the logic of when none of these is true (do you show "Stay at Forecast", or "Okay").

  3. #3
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Nested If Formula problem

    Your 3 formulas rearranged and made as consistent as possible in terms of FW80 and CH80 comparisons.

    =IF(FW80="","Zero Forecast","Okay")

    =IF(FW80>CH80,"Stay at Forecast","Adjust to pink")

    =IF(AND(FW80>=GD80,GD80>=CH80),"Change to Open","Stay at Forecast")

    BE VERY CLEAR that YOUR 1st formula allows FW80 = CH80 AND your 3rd formula allows FW80 = GD80 = CH80, so both formulas allow for equality. My experience leads me to believe that's likely a specification error.

    You have these possibilities.

    Please Login or Register  to view this content.
    What should the results be for each of these cases? I can guess.

    Please Login or Register  to view this content.
    Is that correct? If so, and if NONE of these possibilities should return "Okay", then

    =IF(COUNTBLANK(FW80),"Zero Forecast",IF(AND(CH80<=GD80,GD80<=FW80),"Change to Open",IF(FW80<=CH80,"Adjust to pink","Stay at Forecast")))
    Last edited by hrlngrv; 12-10-2020 at 01:05 AM. Reason: clarification

  4. #4
    Registered User
    Join Date
    09-06-2018
    Location
    Raleigh, NC
    MS-Off Ver
    MS365 V2202
    Posts
    24

    Re: Nested If Formula problem

    This is exactly what I needed. Thank you so much

+ 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. [SOLVED] Nested formula problem.
    By Alias1431 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-29-2014, 04:57 PM
  2. [SOLVED] Problem With Nested Formula
    By Nitin002002 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-15-2014, 08:41 AM
  3. Nested IF formula problem
    By perrier in forum Excel General
    Replies: 4
    Last Post: 10-25-2010, 06:01 PM
  4. Nested IF formula - problem
    By DUAL in forum Excel General
    Replies: 9
    Last Post: 11-25-2008, 06:41 AM
  5. Complex nested array formula problem and challenge
    By PeterWilliams in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-26-2007, 10:34 PM
  6. Problem with nested IF and OR formula?
    By Peter Frank in forum Excel General
    Replies: 4
    Last Post: 03-19-2006, 03:35 PM
  7. [SOLVED] Problem with nested logical formula.
    By Bill R in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-26-2005, 12:05 AM
  8. [SOLVED] Problem with nested IF_OR statement:great formula
    By RagDyeR in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 10:05 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