A2:100 should have been A100...

What does...

=MAX(IF(ISNUMBER(A2:A100+0),A2:A100+0))

return?

I got step 1 figured out.

Just one more step to make it perfect. How do I make the formula alert me if the cell contains an error?

For example, =IF(ISERROR(D3),CHANGE CELL COLOR TO YELLOW?)

is that possible? I already have the cell conditional formatted in 3 different color for other purposes that is more necessary, so can't use anymore conditional formatting.

This alerting purpose just an extra step, so that I know the cell contains error, and it takes a given value instead of its actual value.

==========

The second step, I still couldn't figure out

=MAX(A2:A100)

the formula above will ignore any errors EXCEPT "#VALUE!"

For example, I have the following:

Column A:

5

8

R

TY

#VALUE

=MAX(A1:A5) will returns 8, it ignores all error

=MAX(ISNUMBER(A1:A5),A1:A5) will returns 8 also

=MAX(IF(ISNUMBER(A1:A5),A1:A5)) and

=MAX(IF(ISNUMBER(A1:A5+0),A1:A5+0)) << both formula return 0

but if I change the last value to #VALUE! (just an extra exclamation mark at the end)

=MAX(A1:A5) now returns "#VALUE!"

=MAX(ISNUMBER(A1:A5),A1:A5) returns #VALUE! also

The other 2 formula do not change, they both returns 0

Another issue I found on the spreadsheet I work on is in 1 cell, the formula will calculate ONLY if the cell contains a #, a letter won't work for MAX, MIN, or AVERAGE, I do not know why either, I cleared, reformatted the cell, and the same thing happened (but only 1 cell).

The EXCLAMATION MARK does NOT work for ANY/ALL cells.

I tested it on 3 different computers w/ 3 different versions of Excel, Windows version 2002, Windows version(not sure, computer not present now), and MAC version 2004, and none works.

THANK YOU FOR YOUR HELP!!!