+ Start a Discussion
George AdamsGeorge Adams 

Creating basic formula field (Incorrect number of parameters for function 'IF()')

Hello,

I have what I think is a simple question. I am trying to do the following:
  1. Have the formula look at MK_Days_When_Postmarked__c
  2. If it is blank, run throught the 4 calculations regarding trial date messages
  3. If it is not blank, run TEXT(MK_Days_When_Postmarked__c) (ie. show the number in that field)
 
IF( ISNULL(MK_Days_When_Postmarked__c),

IF(  MK_Days_Into_Trial__c = 0 , "Not Yet Shipped" ,

IF(  MK_Days_Into_Trial__c < 46 , "Day " + TEXT(MK_Days_Into_Trial__c) , 

IF(  MK_Days_Into_Trial__c < 51 , "Manually Calculate (~" + TEXT(MK_Days_Into_Trial__c) +" Days)" , 

IF(  MK_Days_Into_Trial__c > 50 ,"Out of Trial (" + TEXT(MK_Days_Into_Trial__c) + " Days)",

TEXT(MK_Days_When_Postmarked__c)

)
)
)
)
)

There's a good chance I'm making a simple mistake here, just can't seem to find it.

Thanks!
Best Answer chosen by George Adams
R Z KhanR Z Khan
Oh i see. then the formula should be as following
IF( ISNULL(MK_Days_When_Postmarked__c),

IF(  MK_Days_Into_Trial__c = 0 , "Not Yet Shipped" ,

IF(  MK_Days_Into_Trial__c < 46 , "Day " + TEXT(MK_Days_Into_Trial__c) ,

IF(  MK_Days_Into_Trial__c < 51 , "Manually Calculate (~" + TEXT(MK_Days_Into_Trial__c) +" Days)" ,
"Out of Trial (" + TEXT(MK_Days_Into_Trial__c) + " Days)"
)
)
), 
TEXT(MK_Days_When_Postmarked__c)
)

 

All Answers

R Z KhanR Z Khan
Hi Mathew,

seems like your outermost If statement has only 2 parameters. If statement should be IF(condition, ifTrue, ifFalse)
George AdamsGeorge Adams
Thanks for the reply.

My understanding is that my formula is setup as IF( ISNULL(MK_Days_When_Postmarked__c), [all the statements about trial date], [just show what's in MK_Days_When_Postmarked__c]).

Is that not the way I've written it?
R Z KhanR Z Khan
No, the formula is if(ISNULL(
MK_Days_When_Postmarked__c
), [all statements])
TEXT(MK_Days_When_Postmarked__c) belongs to the last inner IF statement. not to the outermost statement. 
 
George AdamsGeorge Adams
I understand what you're saying, but when I modify the code to add a final ifFalse statement, I still get the error but is says I'm giving it too many parameters.
 
IF( ISNULL(MK_Days_When_Postmarked__c),

IF(  MK_Days_Into_Trial__c = 0 , "Not Yet Shipped" ,

IF(  MK_Days_Into_Trial__c < 46 , "Day " + TEXT(MK_Days_Into_Trial__c) , 

IF(  MK_Days_Into_Trial__c < 51 , "Manually Calculate (~" + TEXT(MK_Days_Into_Trial__c) +" Days)" , 

IF(  MK_Days_Into_Trial__c > 50 ,"Out of Trial (" + TEXT(MK_Days_Into_Trial__c) + " Days)",

TEXT(MK_Days_When_Postmarked__c),

"test"

)
)
)
)
)

Expected 3, received 4.
R Z KhanR Z Khan
no,

it hsoudl be before the last ')'
 
IF( ISNULL(MK_Days_When_Postmarked__c),

IF(  MK_Days_Into_Trial__c = 0 , "Not Yet Shipped" ,

IF(  MK_Days_Into_Trial__c < 46 , "Day " + TEXT(MK_Days_Into_Trial__c) ,

IF(  MK_Days_Into_Trial__c < 51 , "Manually Calculate (~" + TEXT(MK_Days_Into_Trial__c) +" Days)" ,

IF(  MK_Days_Into_Trial__c > 50 ,"Out of Trial (" + TEXT(MK_Days_Into_Trial__c) + " Days)",

TEXT(MK_Days_When_Postmarked__c),

)
)
)
), 
"test"
)

 
George AdamsGeorge Adams
Awesome, thank you. That works (after removing that extra comma).

Can you reverse the ifTrue and ifFalse conditions on the outermost IF?

I can't seem to get it working. As in, if ISNULL(MK_Days_When_Postmarked__c) is true, then just run all the IFs, but if not, just run TEXT(MK_Days_When_Postmarked__c).

I reversed the order by accident.
R Z KhanR Z Khan
Hi Mathew,

you can do it by replacing "text" with TEXT(MK_Days_When_Postmarked__c). But what did  you want ot do in the innermsot IF statemebnt. whats suppsoed to happen if MK_Days_Into_Trial__c<=50?
George AdamsGeorge Adams
If MK_Days_Into_Trial__c <= 50, isn't that covered by the following (and all the previous statements?):
 
IF(  MK_Days_Into_Trial__c < 51 , "Manually Calculate (~" + TEXT(MK_Days_Into_Trial__c) +" Days)"

 
George AdamsGeorge Adams
Right now the forumla is just returning 0.
R Z KhanR Z Khan
Oh i see. then the formula should be as following
IF( ISNULL(MK_Days_When_Postmarked__c),

IF(  MK_Days_Into_Trial__c = 0 , "Not Yet Shipped" ,

IF(  MK_Days_Into_Trial__c < 46 , "Day " + TEXT(MK_Days_Into_Trial__c) ,

IF(  MK_Days_Into_Trial__c < 51 , "Manually Calculate (~" + TEXT(MK_Days_Into_Trial__c) +" Days)" ,
"Out of Trial (" + TEXT(MK_Days_Into_Trial__c) + " Days)"
)
)
), 
TEXT(MK_Days_When_Postmarked__c)
)

 
This was selected as the best answer
R Z KhanR Z Khan
Right nwo this is how your formula works
If (MK_Days_When_Postmarked__c is null) then
if(MK_Days_Into_Trial__c == 0) you return Note Yet SHipped
if(MK_Days_Into_Trial__c <46 you return "Day " + TEXT(MK_Days_Into_Trial__c)
if( MK_Days_Into_Trial__c >=51 you return "Out of Trial (" + TEXT(MK_Days_Into_Trial__c) + " Days)"
but if its >=46 AND <51 you return "Manually Calculate (~" + TEXT(MK_Days_Into_Trial__c) +" Days)"
AND if MK_Days_When_Postmarked__c is not null then return MK_Days_When_Postmarked__c

So is this correct?
George AdamsGeorge Adams
Yes, that's exactly how it is supposed to work. Problem is the formula you most recently made is returning 0 in the field.

If I change MK_Days_When_Postmarked__c to any number, however, it returns that number. So it does look like it's copying MK_Days_When_Postmarked__c BUT when that field is blank, it seems to think it's not blank.
R Z KhanR Z Khan
try changing ISNULL(MK_Days_When_Postmarked__c) to ISNULL(MK_Days_When_Postmarked__c) || MK_Days_When_Postmarked__c=0
George AdamsGeorge Adams
Awesome! Thank you so much. That works perfectly.