Hi there,
I'm working on a big piece of work and I've manged to get stuck on something I thought was quite simple. I've created a forumla that works out the percentage change between 2 figures which is (this works fine):
=(G2-C2)/C2)*100*SIGN(C2)/100
However some of these caluclations have 0/0 or 1/0, 0/4, etc, which bring up an error. I want the cell to display "Nil" when an error is preset. Following what I normally do I created this forumla and it doesn't work:
=IF(ISERROR((G2-C2)/C2)*100*SIGN(C2)/100),"Nil",(G2-C2)/C2)*100*SIGN(C2)/100
It just comes up with an error box. I thought I'd then be clever and try to get around this by doing this:
=IF(ISERROR(G2/C2),"nil",(G2-C2)/C2)*100*SIGN(C2)/100
This way returns the correct value when there is no error, but I get the dreaded #VALUE! error when there is. I'm at a loss. Can anyone help? What am I doing wrong?
Thanks for any help in advance. Rob
(Version Excel 2010)
Bookmarks