Please help consolidate this formula so it doesn't exceed the character limit! - Answers - Salesforce Trailblazer Community
Ask Search:
Joanna IturbeJoanna Iturbe 

Please help consolidate this formula so it doesn't exceed the character limit!

This formula is intended to calculate a score based on SAT superscore ranges.  The formula works, but it compiles at 6721 characters so we need help consolidating it to meet the character limit.  Ideas?

IF ( BF_SAT_SS__c >= 1550, 34, 0) + 
IF (AND( BF_SAT_SS__c >= 1510, BF_SAT_SS__c < 1550), 33, 0) + 
IF (AND( BF_SAT_SS__c >= 1480, BF_SAT_SS__c < 1510), 32, 0) + 
IF (AND( BF_SAT_SS__c >= 1440, BF_SAT_SS__c < 1480), 31, 0) + 
IF (AND( BF_SAT_SS__c >= 1410, BF_SAT_SS__c < 1440), 30, 0) + 
IF (AND( BF_SAT_SS__c >= 1370, BF_SAT_SS__c < 1410), 29, 0) + 
IF (AND( BF_SAT_SS__c >= 1330, BF_SAT_SS__c < 1370), 28, 0) + 
IF (AND( BF_SAT_SS__c >= 1300, BF_SAT_SS__c < 1330), 27, 0) + 
IF (AND( BF_SAT_SS__c >= 1220, BF_SAT_SS__c < 1300), 26, 0) + 
IF (AND( BF_SAT_SS__c >= 1180, BF_SAT_SS__c < 1220), 25, 0) + 
IF (AND( BF_SAT_SS__c >= 1140, BF_SAT_SS__c < 1180), 24, 0) + 
IF (AND( BF_SAT_SS__c >= 1110, BF_SAT_SS__c < 1140), 23, 0) + 
IF (AND( BF_SAT_SS__c >= 1070, BF_SAT_SS__c < 1110), 22, 0) + 
IF (AND( BF_SAT_SS__c >= 1030, BF_SAT_SS__c < 1070), 21, 0) + 
IF (AND( BF_SAT_SS__c >= 990, BF_SAT_SS__c < 1030), 20, 0) + 
IF (AND( BF_SAT_SS__c >= 950, BF_SAT_SS__c < 990), 19, 0) + 
IF (AND( BF_SAT_SS__c >= 910, BF_SAT_SS__c < 950), 18, 0) + 
IF (AND( BF_SAT_SS__c >= 870, BF_SAT_SS__c < 910), 17, 0) + 
IF (AND( BF_SAT_SS__c >= 840, BF_SAT_SS__c < 870), 16, 0) + 
IF (AND( BF_SAT_SS__c >= 800, BF_SAT_SS__c < 840), 15, 0) + 
IF (AND( BF_SAT_SS__c >= 780, BF_SAT_SS__c < 800), 14, 0) + 
IF (AND( BF_SAT_SS__c >= 750, BF_SAT_SS__c < 780), 13, 0) + 
IF (AND( BF_SAT_SS__c >= 710, BF_SAT_SS__c < 750), 12, 0) + 
IF (AND( BF_SAT_SS__c >= 680, BF_SAT_SS__c < 710), 11, 0) + 
IF ( BF_SAT_SS__c < 680, 0, 0)
Best Answer chosen by Joanna Iturbe
Steve MolisSteve Molis
I'm not sure what you're trying to do by adding all of those closed IF scores, a field can only hold a single value.  

Could you try using a Nested IF like this?
IF ( BF_SAT_SS__c >= 1550, 34,  
IF ( BF_SAT_SS__c >= 1510, 33,  
IF ( BF_SAT_SS__c >= 1480, 32,  
IF ( BF_SAT_SS__c >= 1440, 31,  
IF ( BF_SAT_SS__c >= 1410, 30,  
IF ( BF_SAT_SS__c >= 1370, 29, 
IF ( BF_SAT_SS__c >= 1330, 28,  
IF ( BF_SAT_SS__c >= 1300, 27, 
IF ( BF_SAT_SS__c >= 1220, 26,  
IF ( BF_SAT_SS__c >= 1180, 25, 
IF ( BF_SAT_SS__c >= 1140, 24,  
IF ( BF_SAT_SS__c >= 1110, 23,  
IF ( BF_SAT_SS__c >= 1070, 22, 
IF ( BF_SAT_SS__c >= 1030, 21, 
IF ( BF_SAT_SS__c >= 990, 20, 
IF ( BF_SAT_SS__c >= 950, 19, 
IF ( BF_SAT_SS__c >= 910, 18,  
IF ( BF_SAT_SS__c >= 870, 17,  
IF ( BF_SAT_SS__c >= 840, 16,  
IF ( BF_SAT_SS__c >= 800, 15,  
IF ( BF_SAT_SS__c >= 780, 14,  
IF ( BF_SAT_SS__c >= 750, 13,  
IF ( BF_SAT_SS__c >= 710, 12,  
IF ( BF_SAT_SS__c >= 680, 11,  
0 ))))))))))))))))))))))))

 

All Answers

Steve MolisSteve Molis
What is your current Compile Size at?

Are any of the fields in thos Formula also Formula Fields?  

If they are can you post those Formulas and their Compile Sizes?
Steve MolisSteve Molis
I'm not sure what you're trying to do by adding all of those closed IF scores, a field can only hold a single value.  

Could you try using a Nested IF like this?
IF ( BF_SAT_SS__c >= 1550, 34,  
IF ( BF_SAT_SS__c >= 1510, 33,  
IF ( BF_SAT_SS__c >= 1480, 32,  
IF ( BF_SAT_SS__c >= 1440, 31,  
IF ( BF_SAT_SS__c >= 1410, 30,  
IF ( BF_SAT_SS__c >= 1370, 29, 
IF ( BF_SAT_SS__c >= 1330, 28,  
IF ( BF_SAT_SS__c >= 1300, 27, 
IF ( BF_SAT_SS__c >= 1220, 26,  
IF ( BF_SAT_SS__c >= 1180, 25, 
IF ( BF_SAT_SS__c >= 1140, 24,  
IF ( BF_SAT_SS__c >= 1110, 23,  
IF ( BF_SAT_SS__c >= 1070, 22, 
IF ( BF_SAT_SS__c >= 1030, 21, 
IF ( BF_SAT_SS__c >= 990, 20, 
IF ( BF_SAT_SS__c >= 950, 19, 
IF ( BF_SAT_SS__c >= 910, 18,  
IF ( BF_SAT_SS__c >= 870, 17,  
IF ( BF_SAT_SS__c >= 840, 16,  
IF ( BF_SAT_SS__c >= 800, 15,  
IF ( BF_SAT_SS__c >= 780, 14,  
IF ( BF_SAT_SS__c >= 750, 13,  
IF ( BF_SAT_SS__c >= 710, 12,  
IF ( BF_SAT_SS__c >= 680, 11,  
0 ))))))))))))))))))))))))

 
This was selected as the best answer
Adam JohnsonAdam Johnson
++Steve, but also

Your Less thans are unnecessary.  as they are covered by the previous steps.  As soon as one condition returns true, it will stop evaluating.  
Joanna IturbeJoanna Iturbe
I see now those < are irrelevant.  Let me try your rec Steve and make sure it meets the character limit and the same formula criteria we're going for, but I think that may be the winner! :)
Steve MolisSteve Molis
Nest IF's are inherently Mutually Exclusive.  So when you're writing a Nested IF (especially one that deals with numbers) think of it like you're assigning Letter Grades based on a Test Score.  You have a stack of tests to be graded, when you assign a grade the test it doesn't go back into the same pile, you put it aside in another pile and never have to look at it and Grade it again.   
 

IF(Score >= 90, "A",
IF(Score >= 80, "B",
IF(Score >= 70, "C",
IF(Score >= 60, "D",
"F"))))

Anyone with a test score >= 90% gets an A and their tests are removed from the pile, the only ones left to be graded are the ones < 90%. 

So there's no need to say: 

IF(Score >= 90, 'A',
IF(AND (Score >= 80, Score < 90), 'B',
IF(AND (Score >= 70, Score < 80), 'C', 
IF(AND (Score >= 60, Score < 70), 'D',
"F"))))
Joanna IturbeJoanna Iturbe
This is a huge help, thank you!