Stacked IF statement not evaluating all "value if false" options

KayZee_2025

I have 6 columns of dates that outline a chronological study process: Phase 1 Forecast date & Actual date, Phase 2 Forecast date & Actual date, and Phase 3 Forecast date & Actual date. I would like to consolidate these 6 dates into 1 column ("IX Study Status") that simply gives the current study status. I wrote a stacked IF statement to grab a Forecast date if an Actual date was blank or an Actual date if that column wasn't blank, starting from Phase 3 and moving backward to Phase 1. However, the formula is only evaluating the Phase 3 Forecast and Actual dates, and isn't continuing to move back to Phase 2 & Phase 1. Does any one have any suggestions what I'm doing wrong? Here's my formula:

=IF(ISBLANK([Phase 3 Studies (A)]@row ), "Phase 3 Expected: " + [Phase 3 Studies (F)]@row , IF(NOT(ISBLANK([Phase 3 Studies (A)]@row )), "Phase 3 Received: " + [Phase 3 Studies (A)]@row , IF(ISBLANK([Phase 2 Studies (A)]@row ), "Phase 2 Expected: " + [Phase 2 Studies (F)]@row , IF(NOT(ISBLANK([Phase 2 Studies (A)]@row )), "Phase 2 Received: " + [Phase 2 Studies (A)]@row , IF(ISBLANK([Phase 1 Studies (A)]@row ), "Phase 1 Expected: " + [Phase 1 Studies (F)]@row , IF(NOT(ISBLANK([Phase 1 Studies (A)]@row )), "Phase 1 Received: " + [Phase 1 Studies (A)]@row ))))))

Find more posts tagged with

Formulas

Accepted answers

KayZee_2025

Thanks, @Monika_lnu. Unfortunately that formula results in the same issue with the IF statement not properly evaluating all of the "value if false" statements (the "IX Formula Test" column in yellow is the result when I plugged in your suggested formula). I've figured out a workaround using 3 helper columns to ingest the forecast and actual result from each of the three phases using this formula:

=IF(ISBLANK([IX Phase 1 Studies (A)]@row ), "IX Phase 1 Expected: " + [IX Phase 1 Studies (F)]@row , "IX Phase 1 Complete")

and then I used the following formula to pick up the phase results in my "IX Study Status" column:

=IF(CONTAINS("Complete", [IX Phase 3 Status]@row ), [IX Phase 3 Status]@row , IF(CONTAINS("Complete", [IX Phase 2 Status]@row ), [IX Phase 2 Status]@row , IF(CONTAINS("Complete", [IX Phase 1 Status]@row ), [IX Phase 1 Status]@row , [IX Phase 1 Status]@row )))

All comments

Monika_lnu

@KayZee_2025 =IF(NOT(ISBLANK([Phase 3 Studies (A)]@row )), "Phase 3 Received: " + [Phase 3 Studies (A)]@row , IF(NOT(ISBLANK([Phase 3 Studies (F)]@row )), "Phase 3 Expected: " + [Phase 3 Studies (F)]@row , IF(NOT(ISBLANK([Phase 2 Studies (A)]@row )), "Phase 2 Received: " + [Phase 2 Studies (A)]@row , IF(NOT(ISBLANK([Phase 2 Studies (F)]@row )), "Phase 2 Expected: " + [Phase 2 Studies (F)]@row , IF(NOT(ISBLANK([Phase 1 Studies (A)]@row )), "Phase 1 Received: " + [Phase 1 Studies (A)]@row , IF(NOT(ISBLANK([Phase 1 Studies (F)]@row )), "Phase 1 Expected: " + [Phase 1 Studies (F)]@row , "No Data"))))))

KayZee_2025

=IF(ISBLANK([IX Phase 1 Studies (A)]@row ), "IX Phase 1 Expected: " + [IX Phase 1 Studies (F)]@row , "IX Phase 1 Complete")

and then I used the following formula to pick up the phase results in my "IX Study Status" column:

DKazatsky2

Hi @KayZee_2025,

The reason the above formulas are not going past the "Phase 3" IFs is because once an IF formula evaluates true or false, the formula ends. Using additional columns as you have stated is one way to combat this, but it can be done in one formula if you string together multiple IF statements using the "+" operator. Additionally, if you are checking for something to be blank, the false of that does not need to check for not blank.

Here is a formula that should give you what you are looking for with one formula and no additional columns required.

=IF([Phase 3 Studies (A)]@row = "", "Phase 3 Expected: " + [Phase 3 Studies (F)]@row , "Phase 3 Received: " + [Phase 3 Studies (A)]@row ) + IF([Phase 2 Studies (A)]@row = "", CHAR(10) + "Phase 2 Expected: " + [Phase 2 Studies (F)]@row , CHAR(10) + "Phase 2 Received: " + [Phase 2 Studies (A)]@row ) + IF([Phase 1 Studies (A)]@row = "", CHAR(10) + "Phase 1 Expected: " + [Phase 1 Studies (F)]@row , CHAR(10) + "Phase 1 Received: " + [Phase 1 Studies (A)]@row )

Hope this helps,

Dave

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.