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

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
>
>
>

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
> >
> >
> >

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,