You need to sign in to do that

Don't have an account?

# Formula Help Request

I'm attempting to create a formula dependent on the "Opportunity Type" field. What I'm looking to do is display a % in the a new field called "Revenue Factor". I have attempted to create what I thought was correct below, but it is not working, I get the following error:

Error: Syntax error. Extra IF

IF ( ISPICKVAL ( Type, "New" ) , (Days_left_in_FY__c - 60) / 365)

IF ( ISPICKVAL ( Type, "Hosting" ) , (Days_left_in_FY__c - 60) / 365)

IF ( ISPICKVAL ( Type, "Renewal" ) , (Days_left_in_FY__c) / 365)

IF ( ISPICKVAL ( Type, "Upgrade" ) , (Days_left_in_FY__c) / 365)

IF ( ISPICKVAL ( Type, "Volume" ) , (100)

IF ( ISPICKVAL ( Type, "Client Integrations" ) , (100)

IF ( ISPICKVAL ( Type, "Prof Services" ) , (100)

IF ( ISPICKVAL ( Type, "Harmony Volume Upside" ) , (100) , null

Any suggestions???

Mikeb66That worked, thanks for your help!!

## All Answers

tstrong

Try this:

IF ( ISPICKVAL ( Type, "New" ) ,

((Days_left_in_FY__c - 60) / 365),IF ( ISPICKVAL ( Type, "Hosting" ) ,

((Days_left_in_FY__c - 60) / 365),IF ( ISPICKVAL ( Type, "Renewal" ) ,

((Days_left_in_FY__c) / 365),IF ( ISPICKVAL ( Type, "Upgrade" ) ,

((Days_left_in_FY__c) / 365),IF ( ISPICKVAL ( Type, "Volume" ) , 100,

IF ( ISPICKVAL ( Type, "Client Integrations" ) , 100,

IF ( ISPICKVAL ( Type, "Prof Services" ) , 100,

IF ( ISPICKVAL ( Type, "Harmony Volume Upside" ) , 100 , null )

)))))))Mikeb66

Error: Compiled formula is too big to execute (5,748 characters). Maximum size is 5,000 charactersThanks, just tried that and got the following error:

tstrong

If Days_Left_In_FY_c is also a formula field, then there could be too much data for the formula field to calculate.

Are there any other Types other than the ones listed here? If not, can you say:

IF (

OR ( ISPICKVAL ( Type, "New" ) ,

ISPICKVAL ( Type, "Hosting" ),

ISPICKVAL ( Type, "Renewal" ) ,

ISPICKVAL ( Type, "Upgrade" ) ,

ISPICKVAL ( Type, "Volume" )) , ((Days_left_in_FY__c - 60) / 365), 100)

This won't calculate a NULL for any other types that don't meet the condition.

Mikeb66

That gets me almost there, there are 3 possible formulas that I need for this:

For "New" & "Hosting" the formula is:

((Days_left_in_FY__c - 60) / 365)

For "Renewal" & "Upgrade" the formula is:

((Days_left_in_FY__c) / 365)

And for All Else, ("Client Integrations" , "Prof Services" , "Harmony Volume Upside") the value should be 100

You are also correct that my custom field called Days_Left_In_FY_c is also a formula field.

Thanks again for your help, I really appreciate it!!

Mikeb66

Do you think that there is a way to add:

( ISPICKVAL ( Type, "Renewal" ) ,

( ISPICKVAL ( Type, "Upgrade" ) , ((Days_left_in_FY__c) / 365), 100))

to the formula? Or something like that to add the other condition for Renewal and Upgrade?

tstrong

I think it would be:

IF (

OR (

ISPICKVAL ( Type, "New"),

ISPICKVAL ( Type, "Hosting")),((Days_left_in_FY__c - 60) / 365),

IF (

OR(

ISPICKVAL (Type, "Renewal"),

(ISPICKVAL ( Type, "Upgrade")), ((Days_left_in_FY__c) / 365), 100)))

You may run into the same formulat calc issues with this though, but it's worth a shot.

Good luck! Let me know if it works.

Mikeb66

Thanks, just tried that but got a different error:

Error: Incorrect parameter for function OR(). Expected Boolean, received Numbertstrong

Ok, let's try this:

IF (

OR (

ISPICKVAL ( Type, "New"),

ISPICKVAL ( Type, "Hosting")),(( Days_left_in_FY__c - 60) / 365),

IF (

OR(

ISPICKVAL (Type, "Renewal"),

ISPICKVAL ( Type, "Upgrade")), ((Days_left_in_FY__c) / 365), 100))

Mine came out ok.

Mikeb66That worked, thanks for your help!!