Excel 2007 : How to manipulate array that displays 12:00 AM to a blank ""

[harry1013]

Hi all,

Currently, I am using this formula {=IF(F1=F2,"",MAX(IF($F$1:$F$100=F1,$D$1:$D$100)))}. Column F are different states/regions (i.e. LA, Detroit, Dallas) while column D are times (i.e. 8:21 AM, 10:03 AM). The way the formula works now is if there is a blank in column D, it will put it as 12:00 AM. For some reason, I can't write a simple if to make the field blank instead of 12:00 AM. Any ideas?

Thanks,

-h

[dip11]

Try that
=IF(F1=F2,"",MAX(IF($F$1:$F$100=F1,$D$1:$D$100,"")))

Should that not work, try:
=IF(F1=F2,"",IF(MAX(IF($F$1:$F$100=F1,$D$1:$D$100))=0,"")

[harry1013]

nope that doesn't work for the table i have, it seems to be giving me the max of the column.

[dip11]

Attach the worksheet then

[harry1013]

one minute need to make a sample sheet out of it

[harry1013]

So the columns have been changed so that B is to E as C is to F. Same principles apply to both.

sample.xlsx

[harry1013]

still cannot get this to work =\

[dip11]

Hm try this:
=IF(D2=D3,"",IF(C2="","",MAX(IF($D$1:$D$100=D2,$C$1:$C$100))))
(with CSE)