Max, Min, Avg of a range containing some #NA

[Pierre]

Hi all,
I have a little problem to solve...
I have a range with 2000 cells (let's say A1:A2000) and it contains some #NA
If doing MAX(A1:A2000) it returns #NA

How can I have a result that does not take the incorrect values into account ?

Thanks to all !!

[mangesh_yadav]

=MAX(IF(ISERROR(A1:A2000),"",A1:A2000))

press control + shift + enter

- Mangesh

[Pierre]

Thanks a lot.
The only problem is that if you select a range longer than the real one, a MIN will return 0 (even if you have oonly positive values) as an empty cell is considered as null

[mangesh_yadav]

=MIN(IF(ISERROR(A1:A5),"",IF(ISBLANK(A1:A5),"",A1:A5)))

- Mangesh

[Domenic]

Another way...

Maximum:

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

Minimum:

=MIN(IF(ISNUMBER(A1:A10),A1:A10))

Minimum, excluding zero values:

=MIN(IF((ISNUMBER(A1:A10)),IF(A1:A10>0,A1:A10)))

These formula need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!