Excluding 'NA' from my KPI formula

Sook Wern Chua

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.

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