Formating #VALUE! cells

[Guest]

I have a few formulas in a spreadsheet that are equal to #VALUE! which is
accurate, but is making my spreadsheet sloppy to read. Is there a way to
format cells to display a specific value if the cell value = #VALUE!. For
example, if cell W10=#VALUE! then display "TRUE" in the cell (instead of
#VALUE!).

Thank you.
Regards,
Diane

 

[JE McGimpsey]

Well, you could use

=IF(ISERR(W10),"TRUE",W10)

but you'd do a lot better to fix the #VALUE! errors in the first place.

Having "acceptable errors" in a worksheet often masks other problems,
and inures the user to unexpected errors.

 

[Myrna Larson]

Presumably you have some formula in those cells. Replace the formujlas with
something like this:

=IF(ISERROR(<your formula>),TRUE,<your formula>)

 

[Guest]

I am doing a bunch of calculations on stocks, 20 days, 60 days, 90 days etc
of data - some stocks are IPOS & currently do not have 90 days of data - but
will in the future - so I don't want to mess with formulas etc - just want
the sheet to MASK the temporary lack of data. I am trying to se the IF(ISERR
function, but due to the impossibilty of using find/replace to add this to
all cells (link issures) I was hoping to use conditional formating. Perhaps
combining the IF(ISSERR function within conditional formating, but at this
point - have not figued out if/how to use this.

 

[JE McGimpsey]

Well, having set up a number of financial/investment applications, I'd
be even more worried about leaving errors around on the worksheet,
especially if you're not the only user. But you *can* use conditional
formatting:

CF1: Formula is =ISERR(A1)
Format: Choose font color the same as the background