What happens if max is blank?

[shadow12345]

I am using the below formula.

=MAX(VLOOKUP(E$49,$A$5:$L$14,5,FALSE),VLOOKUP(E$49,$A$5:$L$14,7,FALSE))

To return the maxium value which is fine if both field have a value but if not then I get an error ....

Easy enough to fix, put a zero in the blank fields.

My question is this, can I avoid having to put the zero... because i plan to also use the same again but with a MIN function... which will give the wrong value (0).


Any ideas?

 

[jim may]

=if(iserror(max(vlookup(e$49,$a$5:$l$14,5,false),vlookup(e$49,$a$5:$l$14,7,false))),"",max(vlookup(e$49,$a$5:$l$14,5,false),vlookup(e$49,$a$5:$l$14,7,false)))

 

[JackDanIce]

Something like:

=IF(AND(MAX(A5:L14)>0,MAX(A7:L14)>0),MAX(VLOOKUP(E$49,$A$5:$L$14,5,FALSE),VLOOKUP(E$49,$A$5:$L$14,7,FALSE)),your condition if maximum value of either range is not higher than 0)

 

[shadow12345]

Thanks Gents, both work just fine.

 

[JackDanIce]

You're welcome

 

[Aladin Akyurek]

I am using the below formula.

=MAX(VLOOKUP(E$49,$A$5:$L$14,5,FALSE),VLOOKUP(E$49,$A$5:$L$14,7,FALSE))

To return the maxium value which is fine if both field have a value but if not then I get an error ....

Easy enough to fix, put a zero in the blank fields.

My question is this, can I avoid having to put the zero... because i plan to also use the same again but with a MIN function... which will give the wrong value (0).


Any ideas?

On Excel 2007 or later...

=MAX(
 IFERRORVLOOKUP(E$49,$A$5:$L$14,5,0),0),
 IFERROR(VLOOKUP(E$49,$A$5:$L$14,7,0),0))

On all versions...

Define BigNum as referring to

=9.99999999999999E+307

Now we can invoke:

=MAX(
   LOOKUP(BigNum,CHOOSE({1,2},0,VLOOKUP(E$49,$A$5:$L$14,5,0))),
   LOOKUP(BigNum,CHOOSE({1,2},0,VLOOKUP(E$49,$A$5:$L$14,7,0))))