Forum Discussion

null null's avatar
null null
Copper Contributor
Apr 30, 2018

Need Formula Help to exclude "N/A"

Hi,

 

I have a spreadsheet that I need help adjusting to formulas on to ensure the accuracy of the data reporting. I currently have it set up to remove "N/A" from the denominator calculations but if I have an entire row of "N/A" it returns a "#DIV/0!" error because it cannot divide by zero. I have tried a few different IF formulas but am not getting accurate calculations. Can someone help me? I have posted a screen shot in the attachments.

Formulas & Functions
Requests

5 Replies

  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Apr 30, 2018

    null null-

     

    Try something like this setup.  See the example file for reference.  You could also use a formula that sets the N/A to 0  i.e. 

     

    =IFERROR(B3/C3,0)

     

    • null null's avatar
      null null
      Copper Contributor
      May 01, 2018

      Ok, I got that formula in and it works perfectly. Now how do I remove the N/A from the total percentage calculation?

      • Matt Mickle's avatar
        Matt Mickle
        Bronze Contributor
        May 01, 2018

        You could use a similar formula.  If you would like the value to come through I believe you want to do the sum of all the numerators / the sum of all denominators.

         

        To my knowledge 0/0 = 0.0% and 1/0 = N/A

         

        Not sure what exactly you're looking for since I don't have the formulas in the spreadsheet to reference.

         

        Try this:

         

Resources