max in column

[Svgmassive]

i am trying to get this function to work and the reason maybe it might have alot to do with the formatting of the cell "[h].mm" when there are no values it is actually 12:00:00 AM.how can i modify the function to work correctly IF(I6>0
AND(COUNT(I$5:I$10),IF(I6>0,I6=MAX(I$5:I$10)))
thanks.

 

[GlennUK]

Can you describe in words what you are trying to do with your formula? It's not easy to guess your intentions by reading a formula that doesn't actually work.

 

[Svgmassive]

trying to get the max value in column

 

[GlennUK]

trying to get the max value in column

if that's all you want, then

=MAX(range_ref)

 

[Svgmassive]

hi glen you are corect now if i try min that's where i encounter the problem.thanks

 

[GlennUK]

What problems? What's in the range where you want the MIN from? Does it contain blanks cells? Does it contain zeros that you want to ignore?

 

[Svgmassive]

Hi Glen
when there is a value of 0 because of the formatting"[h].mm" that is 12:00:00 AM now the if f5>0 now runs when it shouldn't.I don't know if that clarifies it.

 

[GlennUK]

It's not that clear, but here's a guess at an array formula ( entered using Ctrl-Shift-Enter ) that should do it:

=MAX(IF(I$5:I$10>0,I$5:I$10))

 

[Svgmassive]

I finally was able finally to get a conditional formatting with the formula listed below now i am trying to do the same for the max.Can this be done any differently? thanks
=IF($F7>0,IF(TEXT(H7,"[h].mm")=TEXT(SMALL(H$5:H$7,IF(COUNTIF(H$5:H$7,"<0.00"),1,COUNTIF(H$5:H$7,0)+1)),"[h].mm"),-1))