You need to sign in to do that

Don't have an account?

Nichelle Hubley

# How to shorten this formula? Need to keep adding options

Is there a way to combine some of these cases to shorten the formula? I need to keep adding options, but the formula field has reached the character limit.

Specifically, is there a way to combine these conditions, because the output is the same for each conditions

If the field is blank, use the first set of numbers to calculate,

else if the field is bmac2015 or exce2015 or amrs2015 or restocan, use the second set of numbers to calculate,

if the field is full but is not one of those conditions, use the first set of numbers to calculate.

Is there a better way to approach this formula?

The use case is that when the person puts in one of the correct discount codes, the price is calculated accordingly. Right now, there is only one set of discounted pricing. There is the possibility in the future that there would be another set of discounted pricing, so the formula would look like:

If the field is blank, use the first set of numbers to calculate,

else if the field is bmac2015 or exce2015 or amrs2015 or restocan, use the second set of numbers to calculate,

else if the field is *some other conditions*, use a third set of numbers to calculate

if the field is full but is not one of those conditions, use the first set of numbers to calculate.

I hope someone can point me in the right direction with this. I appreciate any help.

Thanks!

IF(ISBLANK(Member_Code__c), ((CASE(TEXT(Annual_Revenue__c), "Up to $100k", 155, "$100k to $1m", 300, "$1m to $5m", 430, "$5m to $10m", 765, "$10m to $20m", 990, 0) + CASE(TEXT(Number_of_Personal_Policies__c),"1", 85, "2", 170, "3", 255, "4", 340, "5", 425, 0))), (CASE(Member_Code__c, "bmac2015", (CASE(TEXT(Annual_Revenue__c), "Up to $100k", 90, "$100k to $1m", 170, "$1m to $5m", 235, "$5m to $10m", 420, "$10m to $20m", 545, 0) + CASE(TEXT(Number_of_Personal_Policies__c),"1", 65, "2", 130, "3", 195, "4", 260, "5", 325, 0)), "exce2015", (CASE(TEXT(Annual_Revenue__c), "Up to $100k", 90, "$100k to $1m", 170, "$1m to $5m", 235, "$5m to $10m", 420, "$10m to $20m", 545, 0) + CASE(TEXT(Number_of_Personal_Policies__c),"1", 65, "2", 130, "3", 195, "4", 260, "5", 325, 0)), "amrs2015", (CASE(TEXT(Annual_Revenue__c), "Up to $100k", 90, "$100k to $1m", 170, "$1m to $5m", 235, "$5m to $10m", 420, "$10m to $20m", 545, 0) + CASE(TEXT(Number_of_Personal_Policies__c),"1", 65, "2", 130, "3", 195, "4", 260, "5", 325, 0)), "restocan", (CASE(TEXT(Annual_Revenue__c), "Up to $100k", 90, "$100k to $1m", 170, "$1m to $5m", 235, "$5m to $10m", 420, "$10m to $20m", 545, 0) + CASE(TEXT(Number_of_Personal_Policies__c),"1", 65, "2", 130, "3", 195, "4", 260, "5", 325, 0)), /*Add new member codes above*/ ((CASE(TEXT(Annual_Revenue__c), "Up to $100k", 155, "$100k to $1m", 300, "$1m to $5m", 430, "$5m to $10m", 765, "$10m to $20m", 990, 0) + CASE(TEXT(Number_of_Personal_Policies__c),"1", 85, "2", 170, "3", 255, "4", 340, "5", 425, 0))))))

Specifically, is there a way to combine these conditions, because the output is the same for each conditions

(CASE(Member_Code__c, "bmac2015", (CASE(TEXT(Annual_Revenue__c), "Up to $100k", 90, "$100k to $1m", 170, "$1m to $5m", 235, "$5m to $10m", 420, "$10m to $20m", 545, 0) + CASE(TEXT(Number_of_Personal_Policies__c),"1", 65, "2", 130, "3", 195, "4", 260, "5", 325, 0)), "exce2015", (CASE(TEXT(Annual_Revenue__c), "Up to $100k", 90, "$100k to $1m", 170, "$1m to $5m", 235, "$5m to $10m", 420, "$10m to $20m", 545, 0) + CASE(TEXT(Number_of_Personal_Policies__c),"1", 65, "2", 130, "3", 195, "4", 260, "5", 325, 0)), "amrs2015", (CASE(TEXT(Annual_Revenue__c), "Up to $100k", 90, "$100k to $1m", 170, "$1m to $5m", 235, "$5m to $10m", 420, "$10m to $20m", 545, 0) + CASE(TEXT(Number_of_Personal_Policies__c),"1", 65, "2", 130, "3", 195, "4", 260, "5", 325, 0)), "restocan", (CASE(TEXT(Annual_Revenue__c), "Up to $100k", 90, "$100k to $1m", 170, "$1m to $5m", 235, "$5m to $10m", 420, "$10m to $20m", 545, 0) + CASE(TEXT(Number_of_Personal_Policies__c),"1", 65, "2", 130, "3", 195, "4", 260, "5", 325, 0)),So, the complete formula would be:

If the field is blank, use the first set of numbers to calculate,

else if the field is bmac2015 or exce2015 or amrs2015 or restocan, use the second set of numbers to calculate,

if the field is full but is not one of those conditions, use the first set of numbers to calculate.

Is there a better way to approach this formula?

The use case is that when the person puts in one of the correct discount codes, the price is calculated accordingly. Right now, there is only one set of discounted pricing. There is the possibility in the future that there would be another set of discounted pricing, so the formula would look like:

If the field is blank, use the first set of numbers to calculate,

else if the field is bmac2015 or exce2015 or amrs2015 or restocan, use the second set of numbers to calculate,

else if the field is *some other conditions*, use a third set of numbers to calculate

if the field is full but is not one of those conditions, use the first set of numbers to calculate.

I hope someone can point me in the right direction with this. I appreciate any help.

Thanks!

SlashApex (Luis Luciani)Hi Nichelle,

I believe the below formula would accomplish the same without having to rewrite so much logic. Please give it a try and let me know how it goes

Good Luck!

## All Answers

SlashApex (Luis Luciani)Hi Nichelle,

I believe the below formula would accomplish the same without having to rewrite so much logic. Please give it a try and let me know how it goes

Good Luck!

SlashApex (Luis Luciani)In the future, if there is a thrid set, then you can do something like this:

Nichelle HubleyLuis, thanks so much for your help - that looks spot on!

I will test it later today and report back.

Nichelle

Nichelle HubleyThere is an error "Error: Incorrect parameter type for function 'TEXT()'. Expected Number, Date, DateTime, Picklist, received Text"

It highlights the first Member_Code__c

JSingHi Nichelle,

I believe the field type for your Member_Code__c field is Text. You can try the below to fix the error:

(1) Change the field type of Member_Code__c to picklist

(2) Remove the TEXT before the Member_Code_c field in your formula

Nichelle HubleyThanks a lot, that was pretty obvious, but sometimes I just can't see the answer staring me in the face. I removed TEXT and it worked perfectly.

Thanks for your help JSing and Luis