Maximum function but excluding error values

[Christian411]

Hi guys,

I think this is probably straight forward but I haven't sorted it yet!

I want to find the maximum numerical value in a range of cells (E7:E50). However, some of the cells in this range contain the error value #N/A. I can't use the MAX worksheet function because when I do, the result it returns is #N/A. I also can't use the DMAX, or MAXA functions.

Can anyone assist?

Thanks in advance,

Chris

[Andy Pope]

You need to use an array formula, commit with CTRL+SHIFT+ENTER

Assuming values in A1:A16 then use

=MAX(IF(ISERROR(A1:A16),,A1:A16))