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

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? 63.2k 15 15 gold badges 53 53 silver badges 104 104 bronze badges asked Mar 2, 2023 at 19:12 Spencer Widman Spencer Widman 3 1 1 silver badge 2 2 bronze badges

2 Answers 2

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

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.