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.
Answers

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?

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

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
Help Article Resources
Categories
Check out the Formula Handbook template!