# 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?

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

• 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! Mar 2 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. Mar 2 at 21:06

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

• 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...` Mar 2 at 19:57
• Good call, forgot about that. will update Mar 2 at 20:54