Can you help me with this formula

The formula was working fine, I added the formula to pull if ATP-CTP find IN in the position column to change Instructor / Student to Instructor put isn't updating.

See the formula below:

=IF(FIND("/I", [INSAV_DSPLY_CMNT_1_TXT]@row) > 0, "Instructor", IF(FIND("/S", [INSAV_DSPLY_CMNT_1_TXT]@row) > 0, "Student", IF(Client@row = "Delta", IF(FIND("IN", Position@row) > 0, "Instructor", IF(Client@row = "ATP-CTP", IF(FIND("IN", Position@row) > 0, "Instructor", IF(Client@row = "Delta", IF(FIND("OQ", [Inst_Role]@row) > 0, "Student", ""))))))))

Find more posts tagged with

Accepted answers

All comments

Brian_Richardson

It looks like you need to set it as a column formula so it updates the whole column.

Louis.Smith

I did that, but even the column that is highlighted didn't update in the picture

Brian_Richardson

Actually it's because of the arrangement of nested IF statements… your formula first checks to see if the Client is "Delta", and if true, it goes on to check for Position has "IN". If Position doesn't have "IN" then it checks to see if Client is "ATP-CTP". But the problem is… that check is only running if the Client is "Delta".

You can adjust that by simply closing the IF statement for Position with a parenthesis, so that the Check for ATP-CTP runs if the Client is NOT "Delta".

Also, I rearranged your formula so that the two "Delta" checks were together instead of being split by the ATP-CTP "IN" check. It's a little less confusing.

=IF(FIND("/I", [INSAV_DSPLY_CMNT_1_TXT]@row) > 0, "Instructor", IF(FIND("/S", [INSAV_DSPLY_CMNT_1_TXT]@row) > 0, "Student", IF(Client@row = "Delta", IF(FIND("IN", Position@row) > 0, "Instructor", IF(FIND("OQ", [Inst_Role]@row) > 0, "Student")), IF(Client@row = "ATP-CTP", IF(FIND("IN", Position@row) > 0, "Instructor")))))

I often find it easier to understand a long chain of IF statements like this if I break it out in bullets like below

IF [INSAV…] contains "/I", "Instructor" ELSE
IF [INSAV…] contains "/S", "Student" ELSE
IF Client is "Delta" THEN
- IF Position contains "IN", "Instructor"
- If Inst_Role contains "OQ", "Student"
- ELSE
If Client is "ATP-CTP" THEN
- If Position contains "IN", "Instructor"

Also, you might find it a little cleaner to use CONTAINS instead of counting FINDS >0. It's not much of a change but it's a little cleaner. That formula would be:

=IF(CONTAINS("/I", [INSAV_DSPLY_CMNT_1_TXT]@row), "Instructor", IF(CONTAINS("/S", [INSAV_DSPLY_CMNT_1_TXT]@row), "Student", IF(Client@row = "Delta", IF(CONTAINS("IN", Position@row), "Instructor", IF(CONTAINS("OQ", [Inst_Role]@row), "Student")), IF(Client@row = "ATP-CTP", IF(CONTAINS("IN", Position@row), "Instructor")))))

If you need to add more hierarchy, nested IFs at different hierarchy levels can become pretty confusing. So an alternate way to do this would be to use AND statements in flat chain of IF statements. Like this:

=IF(CONTAINS("/I", [INSAV_DSPLY_CMNT_1_TXT]@row), "Instructor", IF(CONTAINS("/S", [INSAV_DSPLY_CMNT_1_TXT]@row), "Student", IF(AND(Client@row = "Delta", CONTAINS("IN", Position@row)), "Instructor", IF(AND(Client@row = "Delta", CONTAINS("OQ", [Inst_Role]@row)), "Student", IF(AND(Client@row = "ATP-CTP", CONTAINS("IN", Position@row)), "Instructor")))))

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.