Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Aug 2001
    Location
    Chewelah, Washington, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change #DIV/01! to NA (2002)

    Is there a way when the result of a formula is #DIV/01! to have it show NA?

    We have a formula '' =IF(AND(C69=0,C70=0),"NA",(C69/(C69+C70))) " that counts the Yes and No's for an item and divides the Yes's by the total of the Yes and No answers. If there are only NA's, then the answer is NA. Then this formula, "=AVERAGE(C348:E348)", averages out the percentages for a category. When there are no Yes or No responses, it returns the #DIV/01!.

    I'm entering the 130 items per client chart review in Access and then exporting the results to Excel for the calculations. There are some 850 of these averages on the summary page. Currently, I'm copying that worksheet over to another worksheet and manually entering the NA (there are only 10 of them at the moment). Since the data will be updated periodically there may be more of the "can't divide by zero" results and I would like to come up with a better solution.

    Thanks.

    Sue

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change #DIV/01! to NA (2002)

    Try:

    <pre> =IF(ISERROR(C69/(C69+C70)),"N/A",C69/(C69+C70))
    </pre>

    Legare Coleman

  3. #3
    Lounger
    Join Date
    Aug 2001
    Location
    Chewelah, Washington, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change #DIV/01! to NA (2002)

    Works perfect. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    Thank you.

    Sue

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •