+ Reply to Thread
Results 1 to 5 of 5

remove error value when formula exists for empty cells

  1. #1
    Cyrus
    Guest

    remove error value when formula exists for empty cells

    I'm creating a worksheet where I want to carry the formula down the sheet,
    eventhough cells are blank. I keep getting error values. I understand why
    the error values, but I do not want them to display.

    My formula is: =(B6-B5)/ABS(B5)

    If tired:

    =IF(ISERROR(B7-B6)/ABS(B6),"",(B7-B6)/ABS(B6))

    and

    =IF((B8-B7)/ABS(B7)=0,"")

    but #DIV/0! keeps displaying.

    Is there an option or conditional format that will give me the option to not
    display error values? I have to create worksheets for several other formulas
    and I do not want the error values to display when cells are empty or when
    dividing by zero.

    Thanks,

    Cyrus




  2. #2
    Gizmo63
    Guest

    RE: remove error value when formula exists for empty cells

    Hi Cyrus,

    Very Close, you just need to sort out your brackets.

    > =IF(ISERROR((B7-B6)/ABS(B6)),"",(B7-B6)/ABS(B6))


    hth
    Giz

    "Cyrus" wrote:

    > I'm creating a worksheet where I want to carry the formula down the sheet,
    > eventhough cells are blank. I keep getting error values. I understand why
    > the error values, but I do not want them to display.
    >
    > My formula is: =(B6-B5)/ABS(B5)
    >
    > If tired:
    >
    > =IF(ISERROR(B7-B6)/ABS(B6),"",(B7-B6)/ABS(B6))
    >
    > and
    >
    > =IF((B8-B7)/ABS(B7)=0,"")
    >
    > but #DIV/0! keeps displaying.
    >
    > Is there an option or conditional format that will give me the option to not
    > display error values? I have to create worksheets for several other formulas
    > and I do not want the error values to display when cells are empty or when
    > dividing by zero.
    >
    > Thanks,
    >
    > Cyrus
    >
    >
    >


  3. #3
    Cyrus
    Guest

    RE: remove error value when formula exists for empty cells

    IT WORKED!!!! GREAT THANKS!!!

    "Gizmo63" wrote:

    > Hi Cyrus,
    >
    > Very Close, you just need to sort out your brackets.
    >
    > > =IF(ISERROR((B7-B6)/ABS(B6)),"",(B7-B6)/ABS(B6))

    >
    > hth
    > Giz
    >
    > "Cyrus" wrote:
    >
    > > I'm creating a worksheet where I want to carry the formula down the sheet,
    > > eventhough cells are blank. I keep getting error values. I understand why
    > > the error values, but I do not want them to display.
    > >
    > > My formula is: =(B6-B5)/ABS(B5)
    > >
    > > If tired:
    > >
    > > =IF(ISERROR(B7-B6)/ABS(B6),"",(B7-B6)/ABS(B6))
    > >
    > > and
    > >
    > > =IF((B8-B7)/ABS(B7)=0,"")
    > >
    > > but #DIV/0! keeps displaying.
    > >
    > > Is there an option or conditional format that will give me the option to not
    > > display error values? I have to create worksheets for several other formulas
    > > and I do not want the error values to display when cells are empty or when
    > > dividing by zero.
    > >
    > > Thanks,
    > >
    > > Cyrus
    > >
    > >
    > >


  4. #4
    Registered User
    Join Date
    07-02-2012
    Location
    Western Australia
    MS-Off Ver
    Office 2010
    Posts
    15

    Re: remove error value when formula exists for empty cells

    Hi,

    I have a spreadsheet where I have a formula entered into a cell and dragged down over 4900+ cells. The formula is =LEFT(E2,LEN(E2)-4) effectively it removes the last 4 characters from the cells in the row next to it.

    Now not all of the cells used in the calculation contain data, some are empty. When this formula tries to do the calculation on an empty it returns "#VALUE!" in the output cell. Normally I can just click on the cell and delete the formula for that specific one but when I am dealing with 4900+ cells and at least 20% contain an error this is a VERY time consuming process. I was wondering how do I either fix the issue or apply that piece of code above.

    Cheers,

  5. #5
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,337

    Re: remove error value when formula exists for empty cells

    hello CameronP can you this again on a new thread...

    click above button "Forum" CHoose ---"Excel General " from there you'll see a button "Post New Thread" thanks.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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