Return Max Date or Blank

[smninos]

I am currently using an array to return the MAX date value. This formula will enter 1/0/00 if the referenced cells are blank. I'd like the furmula to LEAVE THE CELL BLANK if the referenced cells are blank.

{=MAX(IF(Bid_Circuits=$A45,Bid_Trim_Completed,""))}

What would a good way to accomplish this be????



Many, many thanks to all.

[NBVC]

Try

=IF(Count(Bid_Circuits)=0,"",MAX(IF(Bid_Circuits=$A45,Bid_Trim_Completed,"")))

[DonkeyOte]

Your post would imply you're returning dates - if so given we can assume 0 to never be a "valid" result you can simply apply a Custom Format to the cell(s) containing the Array of: [=0]"";m/d/yy

The 0 outputs will thus appear and print as blank.

Other than that you're looking at applying a pre-emptive COUNTIFS test I'd say.

[smninos]

NBVC, thank you but it seems that your formula doesn't function as desired UNLESS Donkeyote's custom format is applied which also works with my formula.

I would like to accomplish leaving the cells blank without another array, but this will suffice if nothing else is available....

Thanks to both of you!

[NBVC]

If Bid_Circuits is completely blank then the formula should work.

[DonkeyOte]

NBVC, I suspect it's also important as to whether or not the Date field is blank for all instances of a given record ... hence the COUNTIFS suggestion

I'm not sure why the Custom Format approach isn't viable... it would certainly be my preference for the sake of calculation overhead.

EDIT:

In hindsight a SUMIF should suffice in preference to COUNTIFS

=IF(SUMIF(Bid_Circuits,$A45,Bid_Trim_Completed),MAX(IF(Bid_Circuits=$A45,Bid_Trim_Completed)),"")}