Excluding 'NA' from my KPI formula

Hi Smartsheet Community,

I am trying to exclude 'NA' from my KPI formula

=IF(AND([Days between Submission/Approval]@row > 28, [Trial Type]@row = "Oncology"), 1, IF(AND([Days between Activation/1st Consent]@row > 7, [Trial Type]@row = "Healthy Volunteers"), 1, IF(AND([Days between Activation/1st Consent]@row > 28, [Trial Type]@row = "Oncology"), 1)))

Your help is appreciated.

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

Hey @Sook Wern Chua

What column(s) contains 'NA'?

As an example, I'll insert this in your first IF statement. You can follow this pattern as needed.

=IF(AND([Days between Submission/Approval]@row > 28, [Trial Type]@row = "Oncology", [your NA column]@row<>"NA"), 1, IF(AND([Days between Activation/1st Consent]@row > 7, [Trial Type]@row = "Healthy Volunteers"), 1, IF(AND([Days between Activation/1st Consent]@row > 28, [Trial Type]@row = "Oncology"), 1)))

The <> is another way of writing 'Not Equal To'.

Does this give you what you need?

Sook Wern Chua

almost,

Do I need an AND statement between the two values. For example Submission/Approval row 28, and does not equal NA.

Kelly Moore

@Sook Wern Chua

If the "NA" is being excluded from your IF statements above, you should be able to add it as I have it written. Without seeing your data and where the NA's are occurring, I am assuming they are a part of your IF conditions.

The AND function has the syntax: AND(range1, criteria1, range2, criteria2, range3, criteria3, etc). You can have as many simultaneous conditions as you like between the parentheses.

Did you try the formula as I had it written? Is it filtering the data like wish it to?

Kelly

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.