Adding If Not Blank to Existing Formula

Smartsheet Community:

Do I Add If Is Not Blank to Each Part of My Current Formula or Just at the Beginning?

Current Formula:

=IF(OR(CPI@row = 1, CPI@row <= 0.95), "Green", IF(OR(CPI@row = 0.85, CPI@row <= 0.94), "Yellow", IF(OR(CPI@row = 0, CPI@row <= 0.84), "Red")))

Attempted Modified Formula But Incorrect Response Results:

=IF(OR(NOT(ISBLANK(CPI@row)), CPI@row = 1, CPI@row <= 0.95), "Green", "", IF(OR(NOT(ISBLANK(CPI@row)), CPI@row = 0.85, CPI@row <= 0.94), "Yellow", "", IF(OR(NOT(ISBLANK(CPI@row)), CPI@row = 0, CPI@row <= 0.84), "Red", "")))

Thanks

Find more posts tagged with

Accepted answers

Nick Korna

Hi @Kaleb

I think this formula is what you're after:

=IF(NOT(ISBLANK(CPI@row)), IF(OR(CPI@row = 1, CPI@row >= 0.95), "Green", IF(OR(CPI@row >= 0.85, CPI@row >= 0.94), "Yellow", IF(OR(CPI@row = 0, CPI@row <= 0.84), "Red"))), "")

Output:

If I've misunderstood something, let me know. If not, I hope this helps! 😊

All comments

Nick Korna

Hi @Kaleb

I think this formula is what you're after:

=IF(NOT(ISBLANK(CPI@row)), IF(OR(CPI@row = 1, CPI@row >= 0.95), "Green", IF(OR(CPI@row >= 0.85, CPI@row >= 0.94), "Yellow", IF(OR(CPI@row = 0, CPI@row <= 0.84), "Red"))), "")

Output:

If I've misunderstood something, let me know. If not, I hope this helps! 😊

Kaleb

https://community.smartsheet.com/discussion/comment/361405#Comment_361405

Yes, That Worked! I See The IF(NOT(ISBLANK(CPI@row)) is at The Beginning Only.

Thanks

Nick Korna

No problem, glad it is what you're after.

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.