How to remove NA from Calculation

tchav

Hello,

I am currently using the formula below to get a percentage complete calculation, one of the parameters has the option for NA. How do I include the NA in the formula to make it so it doesn't affect the percentage? It is the bolded parameter below:

=SUM(IF([Paperwork Received]1 = "YES", 0.2, 0), IF([Samples Received]1 = "YES", 0.2, 0), IF([MEC HPLC Received]1 = "YES", 0.2, 0), IF(Hold1 = "YES", 0, 0), IF([Micro Received]1 = "YES", 0.2, 0), IF(Discrepancies1 = "NO", 0.2, 0))

Austin Smith

Can you show a screenshot? What parameter includes NA, [MEC HPLC Received]?

tchav

Yes MEC HPLC Received has the NA

Jeff Reisman

@tchav Try adding in the bold below. It creates a nested IF as the "value if false" if [MEC HPLC Received]1 <> "Yes", so that if the [MEC HPLC Received]1 = "NA" it leaves the cell blank, but if it's not Yes or NA, it sets the cell to 0.

=SUM(IF([Paperwork Received]1 = "YES", 0.2, 0), IF([Samples Received]1 = "YES", 0.2, 0), IF([MEC HPLC Received]1 = "YES", 0.2, IF[MEC HPLC Received]1 = "NA", "", 0)), IF(Hold1 = "YES", 0, 0), IF([Micro Received]1 = "YES", 0.2, 0), IF(Discrepancies1 = "NO", 0.2, 0))

tchav

That didnt work. :(

Paul Newcome

Try an OR statement.

=SUM(IF([Paperwork Received]1 = "YES", 0.2, 0), IF([Samples Received]1 = "YES", 0.2, 0), IF(OR([MEC HPLC Received]1 = "YES", [MEC HPLC Received]1 = "N/A"), 0.2, 0), IF(Hold1 = "YES", 0, 0), IF([Micro Received]1 = "YES", 0.2, 0), IF(Discrepancies1 = "NO", 0.2, 0))

Jeff Reisman

https://community.smartsheet.com/discussion/comment/368981#Comment_368981

I missed a parentheses between IF and [MEC HPLC Received]1 = "NA". Sorry about that:

=SUM(IF([Paperwork Received]1 = "YES", 0.2, 0), IF([Samples Received]1 = "YES", 0.2, 0), IF([MEC HPLC Received]1 = "YES", 0.2, IF([MEC HPLC Received]1 = "NA", "", 0)), IF(Hold1 = "YES", 0, 0), IF([Micro Received]1 = "YES", 0.2, 0), IF(Discrepancies1 = "NO", 0.2, 0))