ShowAll Questionssorted byDate Posted
Joanna Iturbe

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 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 ))))))))))))))))))))))))```

Steve 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 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
++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 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 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 Iturbe
This is a huge help, thank you!