Hiding Error in Cells!!

[slvtenn]

I have a formula in a cell, which reads #VALUE!, that i want to hide if
this error is displayed. My cell formula looks like this.....

=TEXT($K$1-(6-ROW(A1)),"ddd dd")
&IF(AND(DAY($K$1-(6-ROW(A1)))>=10,DAY($K$1-(6-ROW(A1)))<=14),"th",
CHOOSE(MOD(DAY($K$1-(6-ROW(A1))),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))

where do i put the "IF(ISERROR" statement??? Any help would be greatly
appreciated!

[Biff]

Hi!

I remember seeing this formula.

Anyhow........

You need to put the whole thing inside of ISERROR:

=IF(ISERROR(your_formula)),"",your_formula))

That's gonna be a long one, ain't it? <bg>

Maybe you'd rather use conditional formatting to "hide" the error. The
#VALUE! entry will still be there, you just won't see it:

Select the cell that holds the formula.
Goto Format>Conditional Formatting
Formula is: =ISERROR(cell_reference)
Set the font color to be the same as the background color

Another option is to figure out where the error is being generated and try
to rewrite the formula to prevent the error.

Another option is to create a lookup table for the ordinal numbers. That
would eliminate all this:

> &IF(AND(DAY($K$1-(6-ROW(A1)))>=10,DAY($K$1-(6-ROW(A1)))<=14),"th",
> CHOOSE(MOD(DAY($K$1-(6-ROW(A1))),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))

Lots of options!

Biff

"slvtenn" <slvtenn@db-toolco.com> wrote in message
news:1141512102.918363.16330@i40g2000cwc.googlegroups.com...
>I have a formula in a cell, which reads #VALUE!, that i want to hide if
> this error is displayed. My cell formula looks like this.....
>
> =TEXT($K$1-(6-ROW(A1)),"ddd dd")
> &IF(AND(DAY($K$1-(6-ROW(A1)))>=10,DAY($K$1-(6-ROW(A1)))<=14),"th",
> CHOOSE(MOD(DAY($K$1-(6-ROW(A1))),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))
>
> where do i put the "IF(ISERROR" statement??? Any help would be greatly
> appreciated!
>

[Biff]

Clarification:

> Another option is to create a lookup table for the ordinal numbers. That
> would eliminate all this:
>
>> &IF(AND(DAY($K$1-(6-ROW(A1)))>=10,DAY($K$1-(6-ROW(A1)))<=14),"th",
>> CHOOSE(MOD(DAY($K$1-(6-ROW(A1))),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))

A lookup table wouldn't eliminate all that, but all that could be replaced
with this:

&VLOOKUP(DAY($K$1-(6-ROW(A1))),H1:I9,2)

Where H1:I9 is this table:

1..........st
2..........nd
3..........rd
4..........th
21........st
22........nd
23........rd
24........th
31........st

Putting it all together:

=IF(K1="","",TEXT(K1-(6-ROW(A1)),"ddd
dd")&VLOOKUP(DAY(K1-(6-ROW(A1))),H1:I9,2))

Biff

"Biff" <biffinpitt@comcast.net> wrote in message
news:eZzA$L%23PGHA.2012@TK2MSFTNGP14.phx.gbl...
> Hi!
>
> I remember seeing this formula.
>
> Anyhow........
>
> You need to put the whole thing inside of ISERROR:
>
> =IF(ISERROR(your_formula)),"",your_formula))
>
> That's gonna be a long one, ain't it? <bg>
>
> Maybe you'd rather use conditional formatting to "hide" the error. The
> #VALUE! entry will still be there, you just won't see it:
>
> Select the cell that holds the formula.
> Goto Format>Conditional Formatting
> Formula is: =ISERROR(cell_reference)
> Set the font color to be the same as the background color
>
> Another option is to figure out where the error is being generated and try
> to rewrite the formula to prevent the error.
>
> Another option is to create a lookup table for the ordinal numbers. That
> would eliminate all this:
>
>> &IF(AND(DAY($K$1-(6-ROW(A1)))>=10,DAY($K$1-(6-ROW(A1)))<=14),"th",
>> CHOOSE(MOD(DAY($K$1-(6-ROW(A1))),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))
>
> Lots of options!
>
> Biff
>
> "slvtenn" <slvtenn@db-toolco.com> wrote in message
> news:1141512102.918363.16330@i40g2000cwc.googlegroups.com...
>>I have a formula in a cell, which reads #VALUE!, that i want to hide if
>> this error is displayed. My cell formula looks like this.....
>>
>> =TEXT($K$1-(6-ROW(A1)),"ddd dd")
>> &IF(AND(DAY($K$1-(6-ROW(A1)))>=10,DAY($K$1-(6-ROW(A1)))<=14),"th",
>> CHOOSE(MOD(DAY($K$1-(6-ROW(A1))),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))
>>
>> where do i put the "IF(ISERROR" statement??? Any help would be greatly
>> appreciated!
>>
>
>

[slvtenn]

Thanks Biff it worked great!!!

[Biff]

You're welcome. Thanks for the feedback!

Biff

"slvtenn" <slvtenn@db-toolco.com> wrote in message
news:1141597660.226385.203820@i39g2000cwa.googlegroups.com...
> Thanks Biff it worked great!!!
>