Is there a way to get the MAX() of a range of cells that may include some #N/A values?

[jerbes]

I know that MAX() will ignore logical values and text, but I'm having difficulty converting the #N/A cells into "" null text strings.

Any ideas?

[AlKey]

Try this array formula

=MAX(IF(ISNUMBER(A2:A6),A2:A6))

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

Row\Col	A	B
2	456	545
3	25
4	#N/A
5	545
6	**#N/A

[Tony Valko]

Here's another one if using Excel 2010 or later.

Data Range

	A	B	C
1	Value		Max
2	92		96
3	20
4	text
5	58
6	44
7	#N/A
8	96
9	95
10	2
11	------	------	------

This formula entered in C2:

=AGGREGATE(14,6,A2:A10/ISNUMBER(A2:A10),1)

[jerbes]

Thank you AlKey and Tony. Both methods are just what I needed.
I had not run across Aggregate before. It looks like it will be handy in the future.
Thanks.

[Tony Valko]

You're welcome. Thanks for the feedback!