#REF!

[Guest]

If I try to use the max function on a column with a cell with #REF!, I get
#REF! as my result. I tried doing an if statement saying if a cell = #REF!
make it a 0 but it's not working. Anything else I can do?

 

[Guest]

One way

Try, array-entered (press CTRL+SHIFT+ENTER) in say B1:
=MAX(IF(ISNUMBER(A1:A10),A1:A10))

Adapt the range to suit ..

 

[RichardSchollar]

Hi Chris

=MAX(IF(ISNUMBER(A1:A100),A1:A100))

Confirmed with Ctrl+Shift+Enter will exclude any #REFs (this is an
array formula).

Hope this helps!

Richard

 

[Guest]

Hi,

Instead of trying to work around the error, why not fix it?

if you post your formula that is returning the #REF! error I'm sure someone
will suggest a fix for you.

Regards!
Jean-Guy

 

[Guest]

Thanks!

One way

Try, array-entered (press CTRL+SHIFT+ENTER) in say B1:
=MAX(IF(ISNUMBER(A1:A10),A1:A10))

Adapt the range to suit ..

 

[Max]

You're welcome, Chris.