# How to adjust this formula with another IF statement? [duplicate]

We have a formula field that references a picklist field. It should have data in it only for a certain record type, but somehow in migration it was populated on all records. I need to clear out those values. Since it's not possible to clear values from a formula field with Data Loader, I need to update the formula to say if the Stage picklist field is null, then null.

How do I update this formula to include that?

``````    If(AND(
Text(Stage__c) <> "red",
Text(Stage__c) <> "blue",
Text(Stage__c) <> "green"), "Active", "Not Active")
``````

UPDATE: commenter gives great additional context to how to look at this. For anyone wondering, the full solution here was this:

``````    IF(
ISPICKVAL(Stage__c,""),"",

IF(AND(
Text(Stage__c) <> "red",
Text(Stage__c) <> "blue",
Text(Stage__c) <> "green"), "Active", "Not Active"))
``````
• Thanks, maybe it would if I better understood formulas and how translate what's there to a formula referencing a picklist. I don't know enough yet to read what's there and reach a conclusion.
– sna
Oct 28, 2021 at 5:56

You can chain `IF()` in a formula much like you can in code. You just add another `IF()` in the "then" and/or "else" portions.

We can do things a bit cleaner in code than what I'm about to present, but the equivalent to what you're looking for would be

``````if(String.isBlank(Stage__c)){
// then
return null;
}else{
if(Stage__c != 'red' && Stage__c != 'blue' && Stage__c != 'green'){
// then
return 'Active';
}else{
return 'Not Active';
}
}
``````

Translating to a formula from that is pretty simple

``````IF(
ISBLANK(Stage__c),
/* Then */
Stage__c,
IF(
AND(/* stage checking for red/green/blue */),
/* Then */
'Active',
'Not Active'
)
)
``````

As always, indenting formulas like they were code helps you keep track of things (it's really easy to get lost in a sea of parenthesis otherwise)

• Thanks, you're very good at explaining things. I'm afraid I'm new to writing formulas and know nothing about code, so even trying to translate your formula there into something that works...isn't working. I've landed at this:
– sna
Oct 28, 2021 at 5:35
• Stage is a picklist so pretty sure I need the TEXT part because the existing formula uses it. Not sure how after that first 'then' to say if it's blank then null. Also afraid I don't know how to make this look right in comments. IF( ISBLANK(TEXT(Stage__c), TEXT(Stage__c), IF( AND( Text(Stage__c) <> "red", Text(Stage__c) <> "green", Text(Stage__c) <> "blue"), "Active", "Not Active") ) )
– sna
Oct 28, 2021 at 5:41
• @sna There are some functions that can use picklists directly, but most of them do need you to use `TEXT()`. My primary goal here was to give you a way to think about how to nest `IF()` inside one another. Also, comments only allow for limited formatting (which is part of why we generally encourage everyone to not attempt to put code/formulas into comments). Oct 28, 2021 at 13:50
• @sna When you use `IF()`, it's like choosing between two options (and you will always choose one. Choosing none isn't allowed). Like say "if today is Saturday, then don't set my alarm clock. Else, set my alarm clock". Either the alarm clock is set, or it is not set. So in the outermost `IF()`'s "then" part, you already checked to see if `Stage__c` is null. The only thing left to do there is to return a null value (for which I think either `TEXT(Stage__c)` or `null` would work). Oct 28, 2021 at 13:56
• Thanks for the additional context! As it turns out, I was able to accomplish my goal by preceding my original formula with IF(ISPICKVAL(Stage__c,""),"",
– sna
Oct 28, 2021 at 17:25