How to remove NA from Calculation

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

Find more posts tagged with

Formulas

Support

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.