function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Laura Longtine-RosadoLaura Longtine-Rosado 

Formula Help, Picklists and Percentages

Trying to update a formula for a royalty calculation field, we've added a new option this field needs to account for. 

1. Business Unit
2. Cover Type

I'm new to formuals and think (maybe) I setup a basic shell correctly. But it's giving me a syntex error - so now I'm hoping the experts here can help.

Here's what I did...

IF(
   AND(
       Business_Unit__c = '1',
       ISPICKVAL( Cover_Type__c ,'Hardcover'),(0/100),IF(ISPICKVAL( Cover_Type__c ,'Paperback'),(0/100)
       )

IF(
   AND(
       Business_Unit__c = '2',
       ISPICKVAL( Cover_Type__c ,'Hardcover'),(0/100),IF(ISPICKVAL( Cover_Type__c ,'Paperback'),(0/100)
       )

NULL))))

Thanks in advance for your help!!! 
Andrew GAndrew G
Assuming this is your start point, you might end up with quite a complex formula, but a couple of options may be: 
IF(
    AND(
       Business_Unit__c = '1',
       ISPICKVAL( Cover_Type__c ,'Hardcover')
    ),
    (1/100),
    IF(
        AND(
            Business_Unit__c = '1',
            ISPICKVAL( Cover_Type__c, 'Paperback')
        ),
        (2/100),
        IF(
            AND(
                Business_Unit__c = '2',
                ISPICKVAL( Cover_Type__c ,'Hardcover')
            ),
            (3/100),
            IF(
                AND(
                    Business_Unit__c = '2',
                    ISPICKVAL( Cover_Type__c, 'Paperback')
                ),
                (4/100),
                NULL
            )
        )
    )
)
Or you could try with CASE 
CASE(TEXT(Cover_Type__c),
'Hardcover', IF(Business_Unit__c = '1',(1/100),(3/100)),
'Paperback', IF(Business_Unit__c = '1',(2/100),(4/100))
)
or 
CASE(Business_Unit__c ,
'1', IF(TEXT(Cover_Type__c)= 'Hardcover',(1/100),(2/100)),
'2', IF(TEXT(Cover_Type__c)= 'Hardcover',(3/100),(4/100))
)

Which works better will depend on the number of options in each list. Of the CASE options i would use the first as less repetitive use of the TEXT formula.  But it will depend on whether there are just 2 Cover Types, in which case option 2 would be cleaner/clearer.

Regards
Andrew
 
Laura Longtine-RosadoLaura Longtine-Rosado
Hi Andrew - thank you for your response!
I tried it with the options you presented and keep getting an error based on the business unit also being a picklist. Can I not use two picklists for the formula? 
Business unit has several options, and covertype has two options. 
Is there a different way I should do this formula? 
Laura Longtine-RosadoLaura Longtine-Rosado
Here is what I was trying with...
IF(
AND(
Business_Unit__c = 'B1',
ISPICKVAL( Cover_Type__c ,'Hardcover')
),
(65/100),
IF(
AND(
Business_Unit__c = 'B1',
ISPICKVAL( Cover_Type__c, 'Paperback')
),
(15/100),
IF(
AND(
Business_Unit__c = 'B2',
ISPICKVAL( Cover_Type__c ,'Hardcover')
),
(65/100),
IF(
AND(
Business_Unit__c = 'B2',
ISPICKVAL( Cover_Type__c, 'Paperback')
),
(15/100),
IF(
AND(
Business_Unit__c = 'B3',
ISPICKVAL( Cover_Type__c ,'Hardcover')
),
(50/100),
IF(
AND(
Business_Unit__c = 'B3',
ISPICKVAL( Cover_Type__c, 'Paperback')
),
(15/100),
NULL
)
)
)
)))