Formula Field Case Statement checking to see if a value is between two numbers

Question

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?

Answer 1

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.

Answer 2

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