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

  • Kelly Moore
    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?

  • almost,


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

  • Kelly Moore
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!