Formula check: select drop down option based on other columns

Hi everyone,

I'm looking to use a formula that will select one of my drop-down statuses (Rejected) IF the Avg Score is below 50% OR one of the other columns has the selection of "Red Flag (quality/ethical/concern: cursing, condescension, etc)." Otherwise, I want the field to populate the other status (Acceptable). I'm trying to filter specific candidates based on responses to questions we use during phone screening.

I used the formula below, but I'm not sure where I am going wrong. I also included a screen shot for visualization. Thank you for anyone who may be able to help!

=IF(OR([Avg Score]@row < 0.5, [Q1 Response]@row :[Q8 Response]@row = "Red Flag (quality/ethical/concern: cursing, condescension, etc)"), “Rejected”, “Acceptable”)

Find more posts tagged with

Accepted answers

heyjay

=IF(OR(
      [Avg Score]@row > 0.5, 
      COUNTIF([Q1 Response]@row:[Q8 Response]@row, "Text") > 0), 
"Rejected", "Acceptable")

Lets use COUNTIF for your second IF condition.

I noticed that your Rejected and Acceptable are wrapped in fancy quotes, make sure to change them to normal quotes.

All comments

heyjay

=IF(OR(
      [Avg Score]@row > 0.5, 
      COUNTIF([Q1 Response]@row:[Q8 Response]@row, "Text") > 0), 
"Rejected", "Acceptable")

Lets use COUNTIF for your second IF condition.

I noticed that your Rejected and Acceptable are wrapped in fancy quotes, make sure to change them to normal quotes.

kjanke

Thank you so very much! Using the COUNTIF absolutely solved it. I appreciate the second set of eyes.

Also, I didn't even realize they were "fancy" quotes 😅 Many times, I type out my formulas elsewhere so I can play around with different thoughts regarding different options and then copy them into Smartsheet - I didn't even notice that issue! Thanks for the tip!!

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.