Hiding Error in Cells!!

  • Thread starter Thread starter slvtenn
  • Start date Start date
S

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!
 
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"))
Click to expand...

Lots of options!

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

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