Help needed with MedianIF

[Willjcbaker]

Hi all,

I'm pulling my hair out trying to get a medianIF to work.I have two columns, one with time stamps (E), and the other with an elapsed time SUM calculation in it(F).

I want to find the median of all the values in F, which are in a custom date format of [HH]:MM:SS but also exclude any incomplete records that have no time stamp in column E.

What I've tried is: =MEDIAN(IF($E$2:$E$62000<>"0000-00-00 00-00-00",$F$2:$F$62000,))

But it just returns a value of 0. I've tried rewriting the formula to ignore anything that has "$VALUE!" In column F, but that didn't work either.

Please someone help so I can stop pulling my hair out!

 

[Willjcbaker]

Effectively what I replicate is AVERAGEIF, which I already have working.

Also, the sum in column F is based off another time stamp, one for start and one for end (which is column E)

 

[63falcondude]

If there is no time stamp in column E, what is there? Is the cell blank?

If so, try this:

=MEDIAN(IF($E$2:$E$62000<>"",$F$2:$F$62000)) Ctrl Shift Enter

 

[Willjcbaker]

There is always a time stamp, but if someone hasn't completed, it will read as 0000-00-00 00:00:00.

 

[63falcondude]

There is always a time stamp, but if someone hasn't completed, it will read as 0000-00-00 00:00:00.

In that case, try:

=MEDIAN(IF($E$2:$E$62000>0,$F$2:$F$62000)) Ctrl Shift Enter