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

1. ## 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. ## Re: Change #DIV/01! to NA (2002)

Try:

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

3. ## 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
•