Ask Search:
Amber NewcombAmber Newcomb 

Multiple IF Statements error

I can't get this formua to work, I keep getting errors. Error: Syntax error. Extra ',' is the error i get with the formula below.

Please help! Thanks!

IF(Days_Elapsed__c > 14), "No",
    IF(AND(Days_Elapsed__c >0, Days_Elapsed__c <= 14), "Yes", 
       IF(AND(Days_Elapsed__c <=0), "NULL"))
Best Answer chosen by Amber Newcomb
Deepak AnandDeepak Anand
+1 Give this a shot - 
 
IF(
    Days_Elapsed__c <= 0,
    "NULL",
    IF(
        Days_Elapsed__c <= 14,
        "Yes",
        "No"
    )
)


We are short of one occurrence Days_Elapsed__c  of so that should save some compile size.

All Answers

Deepak AnandDeepak Anand
Like this = 
IF(
    Days_Elapsed__c <= 0,
    NULL,
    IF(
        Days_Elapsed__c <= 14,
        "Yes",
        "No"
    )
)
Steve MolisSteve Molis
Do you really want to display the word "NULL"?  If that's what you want then use this
IF(Days_Elapsed__c > 14, "No",
    IF(Days_Elapsed__c > 0, "Yes", 
       IF(Days_Elapsed__c <= 0, "NULL",
NULL)))
Otherwise just 
IF(Days_Elapsed__c > 14, "No",
    IF(Days_Elapsed__c > 0, "Yes", 
NULL))


 
Steve MolisSteve Molis
PS.  Nested IF statements are Mutually Exclusive.  When you're writing a Nested IF (especially one that deals with numbers) think of it like you're assigning Letter Grades based on a Test Score.  You have a stack of tests to be graded, when you assign a grade the test it doesn't go back into the same pile, you put it aside in another pile and never have to look at it and Grade it again.   
 

IF(Score >= 90, "A",
IF(Score >= 80, "B",
IF(Score >= 70, "C",
IF(Score >= 60, "D",
"F"))))

Anyone with a test score >= 90% gets an A and their tests are removed from the pile, the only ones left to be graded are the ones < 90%. 

So you need to be careful how you write and "nest" your IF's in case you need to assign a B+ to anyone who got an 88-89

Also there's no need to say: 

IF(Score >= 90, 'A',
IF(AND (Score >= 80, Score < 90), 'B',
IF(AND (Score >= 70, Score < 80), 'C', 
IF(AND (Score >= 60, Score < 70), 'D',
"F"))))
Amber NewcombAmber Newcomb
Yes I want it to say NULL so we know that the data for those rows is incomplete. Steve your answer worked but the compile size was too big. Error: Compiled formula is too big to execute (5,865 characters). Maximum size is 5,000 characters thats the error I got. can we make this smaller somehow?
 
IF(Days_Elapsed__c > 14, "No",
    IF(Days_Elapsed__c > 0, "Yes", 
       IF(Days_Elapsed__c <= 0, "NULL",
NULL)))

 
Deepak AnandDeepak Anand
+1 Give this a shot - 
 
IF(
    Days_Elapsed__c <= 0,
    "NULL",
    IF(
        Days_Elapsed__c <= 14,
        "Yes",
        "No"
    )
)


We are short of one occurrence Days_Elapsed__c  of so that should save some compile size.
This was selected as the best answer
Steve MolisSteve Molis
Oh crap...  it looks like the dreaded Pizza Compile Size issue.  

In a relatively small Formula like your it usually means you're referencing (and therefore re-compiling) another Formula that is used in a Field within this Formula field. 

The Compile Size in a Formula Field is the Grand Total of that Formula and the Formulas in any other Formula Fields being referenced.  This is known as the Pizza Compile Size (I may have made that last part up)

It's kinda like this:  Say you wanna make a Pepperoni and Onion Pizza (your Formula) and you only have $15 (your Compile Size "budget") 

If your dough costs $3, and sauce costs $3, and cheese costs $3, and pepperoni costs $3, and onions cost $3 you're all set.  But if the Formula for your dough changes and it nows costs $5, or you try to add another topping your Pizza is gonna be over your $15 Budget/Compile Size.  So to build your Pizza you need to reduce the cost/compile size of those ingredients to get back under $15.  

And you can't just say "Well then I'll just throw all my toppings into one $14 formula and then add it to my dough $3 and sauce $3, because it still adds up to $20 and you're over budget.

Also because the other Formula Fields have the same $15 to spend you need to keep your overall cost down or they can't afford your Pizza/Formula either. 

Can you post the Formula Fields, the Formulas inside of them, and any other Formula Fields that they are cross-referencing?  Basically we need to find out where you're getting your dough, sauce, and toppings, what's inside them, how much they cost, and see if there's any fat we can trim.