ISERROR #VALUE ???

[mvatoi]

Hi,

Lets say the cell I'm working on is D3

D3 = A1+A3+A5 (It's a formula, and sometimes it returns "#VALUE")

I want cell D3 to have a formula and if the result in D3 ISERROR, put a value of 1 there.

I'm running into Circular Reference, and don't know how to do it. Any help?


============

Second,

From a long list of data and some of them have "#VALUE"

I want to take MAX, MIN, AVERAGE, but want to ignore the #VALUE

Like:

MAX(A:A) but ignore any cell in (A:A) that = "#VALUE"


THANKS ALL FOR HELPING

 

[ExcelChampion]

Format/Conditional Formatting...

Change to 'Formula is' and then apply:

=ISERROR(A1)

where A1 is the cell that contains the error.

Choose a color on the pattern tab and click Ok.