# 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)))

• ✭✭✭✭✭✭

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!