Dealing with #DIV0! (Can't find a solution that works for me)

[jomann]

Really sorry for posting this as I know there are loads of solutions on here already, however I've tried them and they don't quite work solve my problem. I'm working out percentage change from one year to the next using =(A1-B1)/B1. I don't want to see the inevitable #DIV0! and don't want to use conditional formatting to hide it as I've already got some quite complex CF going on. This is an example of all the permutations I might get

A B C
1 2012 2013 % change
2 0 5 #DIV/0!
3 5 0 -100%
4 0 0 #DIV/0!
5 5 5 0%
6 5 4 -20%
7 4 5 25%




I would like to show "NA" where the values in both A and B are zero, but where A is zero like it is in A2, I would like to show a blank cell. The formulas I've already found on here deal with zeros in A but not where there might be zeros in both. Also some say to use ISERROR and some say you shouldn't use it?

If anyone can help I'll be very grateful. I do a lot of this and it's bugged me for a long time! Sorry for examples, I can't seem to paste them without them squashing up.

 

[Redwolfx]

Try

=IF(AND(A1=0,B1=0),"NA",IFERROR((A1-B1)/B1,""))

 

[RoryA]

You could use:
=IF(B2=0,IF(A2=0,"NA",""),(A2-B2)/B2)

 

[jomann]

Both work, thanks so much guys. Much appreciated.

 

[Redwolfx]

Glad to help. Thanks for the feedback