# How to remove NA from Calculation

Options
✭✭✭✭

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

Tags:

• ✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

Yes MEC HPLC Received has the NA

• ✭✭✭✭✭✭
Options

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

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭
Options

That didnt work. :(

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!