Formula for giving multiple results

%3CLINGO-SUB%20id%3D%22lingo-sub-2125268%22%20slang%3D%22en-US%22%3EFormula%20for%20giving%20multiple%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2125268%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20modify%20the%20following%20formula%20so%20that%20if%20all%20cells%20have%200%20entered%20in%20them%20the%20result%20will%20be%20NA%20otherwise%20it%20will%20calculate%20the%20accuracy%20rate%3B%20Currently%20it%20shows%20%23DIV%2F0!%20if%20all%20the%20cells%20are%200%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D(T2-V2-W2)%2F(T2%2BU2)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22INSEWA_0-1613093353599.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F254378iBF77582A83832CF5%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22INSEWA_0-1613093353599.png%22%20alt%3D%22INSEWA_0-1613093353599.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20greatly%20appreciate%20any%20help!%20I%20have%20tried%20various%20ways%20with%20IF%20but%20nothing%20is%20working%20and%20I%20am%20sure%20it%20must%20be%20able%20to%20be%20done%20LOL%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2125268%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2125629%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20giving%20multiple%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2125629%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F964354%22%20target%3D%22_blank%22%3E%40INSEWA%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWrap%20your%20formula%20with%20%3CSTRONG%3EIFERROR%3C%2FSTRONG%3E%2C%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR((T2-V2-W2)%2F(T2%2BU2)%2C%0A%22NA%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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")