+ Reply to Thread
Results 1 to 5 of 5

Having problems with =IF(ISERROR(),,) formula

  1. #1
    Registered User
    Join Date
    05-18-2012
    Location
    Exeter
    MS-Off Ver
    Excel 2010
    Posts
    20

    Having problems with =IF(ISERROR(),,) formula

    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)
    Last edited by Robioto; 01-25-2013 at 11:17 AM.

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Having problems with =IF(ISERROR(),,) formula

    Try

    =iferror((G2-C12)/C2)*100*SIGN(C2)/100,"")
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Having problems with =IF(ISERROR(),,) formula

    You missed some "(" and ")" in side,
    It may works
    =IF(ISERROR(((G2-C2)/C2)*100*SIGN(C2)/100),"Nil",((G2-C2)/C2)*100*SIGN(C2)/100)
    Quang PT

  4. #4
    Registered User
    Join Date
    05-18-2012
    Location
    Exeter
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Having problems with =IF(ISERROR(),,) formula

    SOLVED

    I just needed some more brackets. I knew it would be simple!

    =IF(ISERROR(G2/C2),"Nil",((G2-C2)/C2)*100*SIGN(C2)/100)

  5. #5
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Having problems with =IF(ISERROR(),,) formula

    Just for the glory..

    As you work with Excel 2010, you can use this.

    =IFERROR(((G2-C2)/C2)*100*SIGN(C2)/100,"Nill")

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1