Max/Min function -- ignore blank cells

[PortoMarco]

Hi

How can I get my max function (and subsequently my min function) to ignore blank cells?

Here is my function:

=MAX(IF(Return!$A$3:$A$306=Analysis!P$63;IF(Return!$C$3:$C$306=Analysis!$L73;IF(Return!$D$3:$D$306=Analysis!$L$60;Return!$P$3:$P$306))))

my three pre-existing conditions work fine.

I want it to return the max value in the P column (3:306) and ignore the empty cells in that range. The problem is that the cells in P3:P306 are not really empty; they all have a vlookup so I guess excel is treating the "empty" cell as actually full (the formula) and returning a zero value.

 

[PortoMarco]

I also tried this:

=MAX(IF(Return!$A$3:$A$306=Analysis!P$63;IF(Return!$C$3:$C$306=Analysis!$L67;IF(Return!$D$3:$D$306=Analysis!$L$60;IF(ISNUMBER(Return!$P$3:$P$306);Return!$P$3:$P$306)))))

 

[NeonRedSharpie]

Min and Max both ignore blank cells. Are you just masking a 0 as blank?

 

[shg]

Maybe ...

=MAX(IF((Return!$A$3:$A$306=Analysis!P$63) 
      * (Return!$C$3:$C$306=Analysis!$L73)
      * (Return!$D$3:$D$306=Analysis!$L$60) 
      * (Return!$P$3:$P$306<>0), 
      Return!$P$3:$P$306))

 

[PortoMarco]

No, that didn't work. I got this to work though:

=LARGE(IF(Return!$A$3:$A$306=Analysis!R$63;IF(Return!$C$3:$C$306=Analysis!$L67;IF(Return!$D$3:$D$306=Analysis!$L$60;Return!$P$3:$P$306)));1)

But the problem is that in the case where there are no observations there is a #NUM! error. I can live with that but, how can I insert an iserror into my formula so that errors come out as -- ?

 

[PortoMarco]

I tried this and I get the "too many arguments" error

=if(iserror(LARGE(IF(Return!$A$3:$A$306=Analysis!P$63;IF(Return!$C$3:$C$306=Analysis!$L67;IF(Return!$D$3:$D$306=Analysis!$L$60;Return!$P$3:$P$306)));1);"--";LARGE(IF(Return!$A$3:$A$306=Analysis!P$63;IF(Return!$C$3:$C$306=Analysis!$L67;IF(Return!$D$3:$D$306=Analysis!$L$60;Return!$P$3:$P$306)));1))

 

[C Moore]

Use IFERROR. =IFERROR(LARGE(.....),"--")