Formula for giving multiple results

Copper Contributor

I am trying to modify the following formula so that if all cells have 0 entered in them the result will be NA otherwise it will calculate the accuracy rate; Currently it shows #DIV/0! if all the cells are 0

 

=(T2-V2-W2)/(T2+U2)

 

INSEWA_0-1613093353599.png

 

I greatly appreciate any help! I have tried various ways with IF but nothing is working and I am sure it must be able to be done LOL

 

4 Replies

@INSEWA 

Wrap your formula with IFERROR, like this:

=IFERROR((T2-V2-W2)/(T2+U2),
"NA")

 

@Twifoo I can't thank you enough! I knew there had to be something I was missing. I greatly appreciate your help with this :) Have a wonderful day!

@INSEWA 

I'm delighted to have helped you.

@INSEWA 

 

You are getting error #DIV/0!, because you have ignored the basics,,  Excel shows the #DIV/0error when a number is divided by zero, or when a formula refers to a cell that has 0 or is blank, as shown in this screen shot you have ZERO in T2,V2, & in U2 also, just below formula works properly since has few values are greater than Zero !! 

 

And to trap the error, you may use IFERROR built in function.

 

=Iferror(T2-V2-W2)/(T2+U2),"")

 

or 

 

=Iferror(T2-V2-W2)/(T2+U2),"NA")