0

I am attempting to write a formula using a case statement that evaluates a field to determine a "level". For example if Total_Donations__c is between 1000.00 and 4999.99 then the result will equal "Level 1". There are 5 levels total. I have tried to put the following together but I am doing it incorrectly and have not been able to figure out how to piece it together.

Case (
    Total_Donations__c,
    (AND(Total_Donations__c > 1000.00, Total_Donations__c < 4999.99), 'Level 1',
        (AND(Total_Donations__c > 5000.00, Total_Donations__c < 9999.99), 'Level 2',
            (AND(Total_Donations__c > 10000.00, Total_Donations__c < 24999.99), 'Level 3',
                (AND(Total_Donations__c > 25000.00, Total_Donations__c < 49999.99), 'Level 4',
                    Total_Donations__c > 50000.00, 'Level 5',
                    ""
                )
            )
        )
    )
)

Can anyone point out what I am doing wrong?

2 Answers 2

0

CASE() is not well-suited to this type of work (sorting a single number into one of a number of buckets). The function just can't work with booleans in the way we would need here.

The documentation on the CASE() function suggests using IF() to return numbers instead, but at that point you're pretty much using IF() to do the real work instead. It starts to make sense to cut out the middleman.

The good news is that what you've shared with us is at least 80% of the way to just using nested IF() instead.

The other issue you have here is a hole in donation amount ranges.
When you check Total_Donations__c < 4999.99 and Total_Donations__c > 5000.00, you're missing the scenario where the total donations are exactly 5000.00. The way math/logic works, the opposite of < is >= (not >).

One other thing to note is that if you fail the check for Total_Donations__c < 4999.99, you can assume that it is greater than or equal to 5000.00. There is no need to explicitly check that.

So putting that all together, you'd end up with

/* IF() always takes 3 arguments */
/* the condition, the value when the condition is true, the value when the condition is false */
/* By chaining additional IF(), we can make an if-else if-else */
IF(
    AND(Total_Donations__c > 1000.00, Total_Donations__c < 5000.00),
    "Level 1",
    IF(
        Total_Donations__c < 10000.00,
        "Level 2",
        IF(
            Total_Donations__c < 25000.00,
            "Level 3",
            /* and so on */
        )
    )
)

Do keep in mind that donation totals less than 1000 would also fail all of the IF() conditions and end up spitting out "Level 5" for you. I doubt that's what you want, so you'll need to do something to handle that scenario too.

2
  • Thank you Derek F. for the detailed answer. That is exactly what I needed. CASE didn't feel right but I couldn't fill in the gaps. Thank you for the education! Commented Mar 2, 2023 at 20:32
  • The one big benefit of the case statement is built-in readability. The nested if structure can be hard to read/maintain beyond a few ifs IMO.
    – Rob Davis
    Commented Mar 2, 2023 at 21:06
0

Your Case statement should start with 1 (NOT TRUE/FALSE), not Total_Donations__c. You're evaluating the following boolean checks against that value. It also doesn't look like you have a default value which needs to follow 'Level 5'.

I'd recommend formatting your formulas with a new line for each new value for readability.

Case(1,
  IF(AND(...asdf), 1, 0), 'Level 1',
  IF(AND(...fdsa), 1, 0), 'Level 2',
  ...,
  'Default'
)

https://help.salesforce.com/s/articleView?id=sf.customize_functions_case.htm&type=5

2
  • Well, yes and no. That documentation that you link specifically says you can't use true/false in CASE() like this. CASE functions can’t contain functions that return true or false. Instead, make true or false expressions return numbers...
    – Derek F
    Commented Mar 2, 2023 at 19:57
  • Good call, forgot about that. will update
    – Rob Davis
    Commented Mar 2, 2023 at 20:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .