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

**jerbes** · 03-31-2015, 07:57 PM

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** · 03-31-2015, 08:03 PM

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** · 03-31-2015, 08:12 PM

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** · 04-02-2015, 12:35 PM

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** · 04-02-2015, 01:33 PM

You're welcome. Thanks for the feedback!

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Dynamic / Named Print Range To Include Another Range of Cells

Print only range of cells that include data, even if all cells formatted

Subtotal range of values that include errors

Do the values in a range of cells include all members of a set?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions