+ Reply to Thread
Results 1 to 2 of 2

#Value in nested IF statement

  1. 06-21-2017, 05:36 PM #1
    gregoc43
    gregoc43 is offline
    Registered User
    Join Date
    06-21-2017
    Location
    Nashville
    MS-Off Ver
    2010
    Posts
    1

    #Value in nested IF statement

    Basically I'm trying to make a formula that spits out a value based on a long series of nested IF statements. Here's what I've written so far. I made a major edit to the formula but it said I had entered too many arguments. Once I removed some extra parentheses it told me the error was fixed but it was still giving me a #VALUE message where it shouldn't have. Here's what I currently have:

    '=IF(IF((B31-B32)>0.3,IF(B31<4,B31-(B31-B32)*0.2,IF(B31<4.5,B31-(B31-B32)*0.25,IF(B31<5,B31-(B31-B32)*0.3,IF(B31<5.5,B31-(B31-B32)*0.35,IF(B31<6,B31-(B31-B32)*0.4,IF(B31<=6.5,B31-(B31-B32)*0.45,IF(B31<=7,B31-(B31-B32)*0.5,"Destroy"))))))),IF(B31<4, B31-0.1, IF(B31<=4.5, B31-0.15, IF(B31<=5, B31-0.2, IF(B31<=6, B31-0.25, IF(B31<=7, B31-0.3, IF(B31>7,"Destroy","Error, check formula"))))))) >B31,IF(B31<4,B31,IF(B31<4.5,B31-0.1,IF(B31<5,B31-0.15,IF(B31<6,B31-0.2,IF(B31<=7,B31-0.25, IF(B31>7, "Destroy", "Error, check formula"))))))) ,IF((B31-B32)>0.3,IF(B31<4,B31-(B31-B32)*0.2,IF(B31<4.5,B31-(B31-B32)*0.25,IF(B31<5,B31-(B31-B32)*0.3,IF(B31<5.5,B31-(B31-B32)*0.35,IF(B31<6,B31-(B31-B32)*0.4,IF(B31<=6.5,B31-(B31-B32)*0.45,IF(B31<=7,B31-(B31-B32)*0.5,"Destroy"))))))),B32)


    B31 in this case is 5 and B32 is 4. The output is supposed to be 4.65 but we're still getting the #VALUE problem. We were getting the right number at first but then I had to add in something into the formula (see underlined) and that's when we started seeing the error. Orginally the underlined portion just said B32 but I had to change that. Any ideas on how to fix this? Thanks for reading.
    Reply With QuoteRegister To Reply
  2. 06-21-2017, 07:59 PM #2
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: #Value in nested IF statement

    '=IF(IF((B31-B32)>0.3,IF(B31<4,B31-(B31-B32)*0.2,IF(B31<4.5,B31-(B31-B32)*0.25,IF(B31<5,B31-(B31-B32)*0.3,IF(B31<5.5,B31-(B31-B32)*0.35,IF(B31<6,B31-(B31-B32)*0.4,IF(B31<=6.5,B31-(B31-B32)*0.45,IF(B31<=7,B31-(B31-B32)*0.5,"Destroy"))))))),IF(B31<4, B31-0.1, IF(B31<=4.5, B31-0.15, IF(B31<=5, B31-0.2, IF(B31<=6, B31-0.25, IF(B31<=7, B31-0.3, IF(B31>7,"Destroy","Error, check formula"))))))) >B31

    ,IF(B31<4,B31,IF(B31<4.5,B31-0.1,IF(B31<5,B31-0.15,IF(B31<6,B31-0.2,IF(B31<=7,B31-0.25, IF(B31>7, "Destroy", "Error, check formula"))))))) ,IF((B31-B32)>0.3,IF(B31<4,B31-(B31-B32)*0.2,IF(B31<4.5,B31-(B31-B32)*0.25,IF(B31<5,B31-(B31-B32)*0.3,IF(B31<5.5,B31-(B31-B32)*0.35,IF(B31<6,B31-(B31-B32)*0.4,IF(B31<=6.5,B31-(B31-B32)*0.45,IF(B31<=7,B31-(B31-B32)*0.5,"Destroy"))))))),B32)

    The red Part always resolves to a number less than B31, or to text... neither of which is ever >B31. So the outer-most test is always = False.
    The big blue parenthesis caused your Value error; it belonged at the end. However, I've pared down the whole thing to this:


    =IF(B31>=7,"Destroy",IF(YourNewTestHere,B31-CHOOSE(SUM(1,--(B31<{6,5,4.5,4})),0.25,0.2,0.15,0.1,0),IF(B31-B32>0.03,B31-(B31-B32)*CHOOSE(SUM(1,--(B31<{6.5,6,5.5,5,4.5,4})),0.5,0.45,0.4,0.35,0.3,0.25,0.2),B32)))

    BTW, you need to decide whether your grouped tests are < or <=. They should all be one or the other. I went with < because that yields your stated "correct" result = 4.65, but it also puts [B31=7] out-of-bounds unless you fudge the "Destroy" range.

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Last edited by leelnich; 06-26-2017 at 08:53 PM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Nested IF statement error. Nested True statement is not triggering
    By Lucas7040 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-26-2016, 11:41 AM
  2. [SOLVED] if statement referencing a nested if(mid) statement
    By kantjustice in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-14-2016, 06:27 PM
  3. [SOLVED] Nested IF Statement with OR Clause Only Processes First IF Statement
    By ExcelQuestFL in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-09-2016, 04:30 PM
  4. nested IF statement or AND? trying to write an IF statement with multiple conditions
    By kcphila in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-09-2015, 04:25 PM
  5. [SOLVED] If statement to select data - nested statement - assistance
    By petitesouris in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-29-2015, 09:55 PM
  6. [SOLVED] Nested If Statement - ISBLANK and ISNA in same statement?
    By Janc in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-23-2013, 09:00 AM
  7. Nested IF Statement-How can I write a Nested IF statement
    By Brandy in forum Excel General
    Replies: 6
    Last Post: 01-14-2009, 06:59 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1