another If and OR conundrum

I want to create a formula the enters a the following percentage based on a picklist (default is 20) unless the product line is one of those specified.

OSI Business Units (picklist)
	CSG Bank Acct Mgmt	34
	CSG Bank Sales	34
	CSG CU Acct Mgmt	30
	CSG CU Sales - Major	30
	INTL	30
	IPTG Acct Mgmt	15
	IPTG Sales	15
	OSC-BSG	30
	OSC Core AM	30
	All Others	20
except when the opportunity is for one of these Product Lines
Product Line (picklist)
	Internet - ASP	40
	Internet - In-House	40
	Lending - Commercial Velocity	40
	Lending - Consumer Streamlend	40
	Lending - Consumer Velocity	40
	Lending - Velocity Conversion	40

Here's what I have tried so far:

IF(ISPICKVAL(OSI_Business_Unit__c , "CSG Bank Acct Mgmt", "CSG Bank Sales",34, 20),
IF(ISPICKVAL(OSI_Business_Unit__c , "CSG CU Acct Mgmt", "CSG CU Sales - Major","OSC-BSG","OSC Core AM",30, 20),
IF(ISPICKVAL(OSI_Business_Unit__c , "IPTG Acct Mgmt", "IPTG Sales",15, 20), 20),
IF( ISPICKVAL( Product_Line__c , "Internet - In-House", "Internet - ASP", "Lending - Consumer Streamlend", "Lending - Consumer Velocity", "Lending - Commercial Velocity","Lending - Velocity Conversion", 40, 20),20)
)
)

but I keep getting syntax errors - can someone show me how to write this?

October 3, 2008
·
Like
0
·
Follow
0

Best Answer chosen by Admin (Salesforce Developers)

jisaac
Finally got it - it was the problem in the very last set where the alternative (false) answer needed to be before the closing paragraph.

I did as you suggested - one If statement at a time - thanks for your help and suggestions!

Jane

if(
     or(
         ispickval( Product_Line__c ,"Internet - ASP")
        ,ispickval(Product_Line__c ,"Internet - In-House")
        ,ispickval(Product_Line__c ,"Internet - In-House")
        ,ispickval(Product_Line__c ,"Lending - Commercial Velocity")
        ,ispickval(Product_Line__c ,"Lending - Consumer Streamlend")
        ,ispickval(Product_Line__c ,"Lending - Consumer Velocity")
        ,ispickval(Product_Line__c ,"Lending - Velocity Conversion")
     ), 40
,if(
    or(
       ispickval( OSI_Business_Unit__c ,"CSG CU Sales - Major")
      ,ispickval(OSI_Business_Unit__c ,"INTL")
      ,ispickval(OSI_Business_Unit__c ,"OSC Core AM")
      ,ispickval(OSI_Business_Unit__c ,"OSC-BSG")
      ,ispickval(OSI_Business_Unit__c ,"CSG CU Acct Mgmt")
    ), 30
,if(
    or(
       ispickval(OSI_Business_Unit__c ,"CSG Bank Acct Mgmt")
       ,ispickval(OSI_Business_Unit__c ,"CSG Bank Sales")
    ), 34
,if(
    or(
       ispickval(OSI_Business_Unit__c ,"IPTG Acct Mgmt")
       ,ispickval(OSI_Business_Unit__c ,"IPTG Sales")
    ), 15,20
)
)
)
)

October 7, 2008
·
Like
0
·
Dislike
0

Jakester
Are you talking about updating the standard Percentage field in Salesforce? Or did you create a custom percentage field? I'm confused, because it sounds like you want a percentage field that can only be one of a few possible numbers, none of which can ever reach 100%...

October 3, 2008
·
Like
0
·
Dislike
0

jisaac
This is a custom percentage field. We take off a certain percentage of the total amount if there are third party fees in the deal. That percentage varies based on the Business Unit and sometimes by the Product Line.

Message Edited by jisaac on 10-03-2008 01:08 PM

October 3, 2008
·
Like
0
·
Dislike
0

Jakester

Something like this, then. Keep in mind that you didn't give me the field names, so Product Line is probably something like Product_Line__c, but I couldn't be sure so I didn't guess. Same thing for OSI Business Units. Also, I only gave you a couple of examples to get started - you'll need to flesh out the rest.

Code:
if( or( ispickval(Product Line,"Internet - ASP") ,ispickval(Product Line,"Internet - In-House") ), 40 ,if( or( ispickval(OSI Business Units,"IPTG Sales") ,ispickval(OSI Business Units,"INTL") ,ispickval(OSI Business Units,"OSC Core AM") ), 30 ,if( or( ispickval(OSI Business Units,"CSG Bank Acct Mgmt") ,ispickval(OSI Business Units,"CSG Bank Sales") ), 34 ) ),0 )

October 3, 2008
·
Like
0
·
Dislike
0

jisaac
Thanks Jakester,

I really appreciate your help on this and I like the way you lay it out. I think we are close but still a problem:

Here's what I did so far and I am getting an error message: "Incorrect number of parameters for Function IF(). Expected 3, Received 2." Any idea where and why?

if(
     or(
         ispickval( Product_Line__c ,"Internet - ASP")
        ,ispickval(Product_Line__c ,"Internet - In-House")
        ,ispickval(Product_Line__c ,"Internet - In-House")
        ,ispickval(Product_Line__c ,"Lending - Commercial Velocity")
        ,ispickval(Product_Line__c ,"Lending - Consumer Streamlend")
        ,ispickval(Product_Line__c ,"Lending - Consumer Velocity")
        ,ispickval(Product_Line__c ,"Lending - Velocity Conversion")
     ), 40
,if(
    or(
       ispickval( OSI_Business_Unit__c ,"CSG CU Sales - Major")
      ,ispickval(OSI_Business_Unit__c ,"INTL")
      ,ispickval(OSI_Business_Unit__c ,"OSC Core AM")
      ,ispickval(OSI_Business_Unit__c ,"OSC-BSG")
      ,ispickval(OSI_Business_Unit__c ,"CSG CU Acct Mgmt")
    ), 30
,if(
    or(
       ispickval(OSI_Business_Unit__c ,"CSG Bank Acct Mgmt")
       ,ispickval(OSI_Business_Unit__c ,"CSG Bank Sales")
    ), 34
,if(
    or(
       ispickval(OSI_Business_Unit__c ,"IPTG Acct Mgmt")
       ,ispickval(OSI_Business_Unit__c ,"IPTG Sales")
    ), 15
),20
)
)
)

October 4, 2008
·
Like
0
·
Dislike
0

Jakester
You're welcome-

I'm pretty sure all you need to do is put the ",20" towards the bottom of the formula after the second-to-last close parenthesis, like so:

Code:
... ,if( or( ispickval(OSI_Business_Unit__c ,"IPTG Acct Mgmt") ,ispickval(OSI_Business_Unit__c ,"IPTG Sales") ), 15 ) ) ),20 )

October 4, 2008
·
Like
0
·
Dislike
0

jisaac
Rats - still getting the same error message even after moving it like you suggested. I wish I understood the logic better so it would not feel like I am stumbling around in the dark. Someday I am going to learn how to write code....

Any other ideas?

October 4, 2008
·
Like
0
·
Dislike
0

Jakester
Work backwards. Start with just one if() and get it working. Then add an or() into it, then add another if(). The basic ideas are these:

If(logical test, what to do if true, what to do if false)

Or(logical test, logical test, logical test, etc) - if any of the tests are True, then you get a True result.

So, here's a simple if() example:

Code:
if(ispickval(Product Line,"Internet - ASP"), 40, 0)

That's saying "if the product line picklist is set to "Internet - ASP" then return 40, if it's not, return 0"

to add an Or() into the mix to allow the picklist to be two values:

Code:
if(
or(
ispickval(Product Line,"Internet - ASP") ,ispickval(Product Line,"Internet - ASP")
),40,0
)

In order to add additional If() statements, you replace the spot where I've been putting a 0 (the "what to do if the logical test is false" area) with a new If() statement.

Everything else is just testing, carefully adding on, counting parenthesis, and a fair amount of cussing <grin>.

Good luck!

-Jake

October 4, 2008
·
Like
0
·
Dislike
0

This was selected as the best answer

jisaac
One correction in the formula (in case anyone else cares). Since I wanted the resulting entries to be percentages (with no decimal points) I had to correct the values in the formula. Instead of 30, I had to use 0.3. Instead of 15, I had to use 0.15. etc. etc.

Jane

Message Edited by jisaac on 10-07-2008 04:14 PM

October 7, 2008
·
Like
0
·
Dislike
0

Jakester
Good job, Jane! Well done.

October 7, 2008
·
Like
0
·
Dislike
0

You need to sign in to do that.

Need an account? Sign Up

Have an account? Sign In

Dismiss

Browse by Topic

Welcome to Support!

Show

sorted by

another If and OR conundrum

All Answers

You need to sign in to do that.