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!

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 FormatConditional 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" wrote in message
ups.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!

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" wrote in message
...
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 FormatConditional 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" wrote in message
ups.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!

Thanks Biff it worked great!!!

You're welcome. Thanks for the feedback!

Biff

"slvtenn" wrote in message
oups.com...
Thanks Biff it worked great!!!