software

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)),quot;ddd ddquot;)
amp;IF(AND(DAY($K$1-(6-ROW(A1)))gt;=10,DAY($K$1-(6-ROW(A1)))lt;=14),quot;thquot;,
CHOOSE(MOD(DAY($K$1-(6-ROW(A1))),10) 1,quot;thquot;,quot;stquot;,quot;ndquot;,quot;rdquot;,quot;thquot;,quot;thquot;,quot;thquot; ,quot;thquot;,quot;thquot;,quot;thquot;))

where do i put the quot;IF(ISERRORquot; 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)),quot;quot;,your_formula))

That's gonna be a long one, ain't it? lt;bggt;

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

Select the cell that holds the formula.
Goto Formatgt;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:

gt; amp;IF(AND(DAY($K$1-(6-ROW(A1)))gt;=10,DAY($K$1-(6-ROW(A1)))lt;=14),quot;thquot;,
gt; CHOOSE(MOD(DAY($K$1-(6-ROW(A1))),10) 1,quot;thquot;,quot;stquot;,quot;ndquot;,quot;rdquot;,quot;thquot;,quot;thquot;,quot;thquot; ,quot;thquot;,quot;thquot;,quot;thquot;))

Lots of options!

Biff

quot;slvtennquot; gt; wrote in message ups.com...
gt;I have a formula in a cell, which reads #VALUE!, that i want to hide if
gt; this error is displayed. My cell formula looks like this.....
gt;
gt; =TEXT($K$1-(6-ROW(A1)),quot;ddd ddquot;)
gt; amp;IF(AND(DAY($K$1-(6-ROW(A1)))gt;=10,DAY($K$1-(6-ROW(A1)))lt;=14),quot;thquot;,
gt; CHOOSE(MOD(DAY($K$1-(6-ROW(A1))),10) 1,quot;thquot;,quot;stquot;,quot;ndquot;,quot;rdquot;,quot;thquot;,quot;thquot;,quot;thquot; ,quot;thquot;,quot;thquot;,quot;thquot;))
gt;
gt; where do i put the quot;IF(ISERRORquot; statement??? Any help would be greatly
gt; appreciated!
gt;
Clarification:

gt; Another option is to create a lookup table for the ordinal numbers. That
gt; would eliminate all this:
gt;
gt;gt; amp;IF(AND(DAY($K$1-(6-ROW(A1)))gt;=10,DAY($K$1-(6-ROW(A1)))lt;=14),quot;thquot;,
gt;gt; CHOOSE(MOD(DAY($K$1-(6-ROW(A1))),10) 1,quot;thquot;,quot;stquot;,quot;ndquot;,quot;rdquot;,quot;thquot;,quot;thquot;,quot;thquot; ,quot;thquot;,quot;thquot;,quot;thquot;))

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

amp;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=quot;quot;,quot;quot;,TEXT(K1-(6-ROW(A1)),quot;ddd
ddquot;)amp;VLOOKUP(DAY(K1-(6-ROW(A1))),H1:I9,2))

Biff

quot;Biffquot; gt; wrote in message
...
gt; Hi!
gt;
gt; I remember seeing this formula.
gt;
gt; Anyhow........
gt;
gt; You need to put the whole thing inside of ISERROR:
gt;
gt; =IF(ISERROR(your_formula)),quot;quot;,your_formula))
gt;
gt; That's gonna be a long one, ain't it? lt;bggt;
gt;
gt; Maybe you'd rather use conditional formatting to quot;hidequot; the error. The
gt; #VALUE! entry will still be there, you just won't see it:
gt;
gt; Select the cell that holds the formula.
gt; Goto Formatgt;Conditional Formatting
gt; Formula is: =ISERROR(cell_reference)
gt; Set the font color to be the same as the background color
gt;
gt; Another option is to figure out where the error is being generated and try
gt; to rewrite the formula to prevent the error.
gt;
gt; Another option is to create a lookup table for the ordinal numbers. That
gt; would eliminate all this:
gt;
gt;gt; amp;IF(AND(DAY($K$1-(6-ROW(A1)))gt;=10,DAY($K$1-(6-ROW(A1)))lt;=14),quot;thquot;,
gt;gt; CHOOSE(MOD(DAY($K$1-(6-ROW(A1))),10) 1,quot;thquot;,quot;stquot;,quot;ndquot;,quot;rdquot;,quot;thquot;,quot;thquot;,quot;thquot; ,quot;thquot;,quot;thquot;,quot;thquot;))
gt;
gt; Lots of options!
gt;
gt; Biff
gt;
gt; quot;slvtennquot; gt; wrote in message
gt; ups.com...
gt;gt;I have a formula in a cell, which reads #VALUE!, that i want to hide if
gt;gt; this error is displayed. My cell formula looks like this.....
gt;gt;
gt;gt; =TEXT($K$1-(6-ROW(A1)),quot;ddd ddquot;)
gt;gt; amp;IF(AND(DAY($K$1-(6-ROW(A1)))gt;=10,DAY($K$1-(6-ROW(A1)))lt;=14),quot;thquot;,
gt;gt; CHOOSE(MOD(DAY($K$1-(6-ROW(A1))),10) 1,quot;thquot;,quot;stquot;,quot;ndquot;,quot;rdquot;,quot;thquot;,quot;thquot;,quot;thquot; ,quot;thquot;,quot;thquot;,quot;thquot;))
gt;gt;
gt;gt; where do i put the quot;IF(ISERRORquot; statement??? Any help would be greatly
gt;gt; appreciated!
gt;gt;
gt;
gt;
Thanks Biff it worked great!!!You're welcome. Thanks for the feedback!

Biff

quot;slvtennquot; gt; wrote in message oups.com...
gt; Thanks Biff it worked great!!!
gt;