Find the highest number and lowest number from I4:I20?

[PhilipeC]

I would like a formula to find the highest number above 0 from I4:I20 and another formula to find the lowest number below 0 from I4:I20?

 

[mrhstn]

Perhaps

=MAXIFS(I4:I20, I4:I20,">0")
=MINIFS(I4:I20, I4:I20,">0")

Do you see any pattern forming based on your previous questions?

 

[PhilipeC]

It appears #NAME !

 

[Marcelo Branco]

Try

=MAX(I4:I20)
=MIN(I4:I20)

M.

 

[footoo]

Duplicate post : https://www.mrexcel.com/forum/excel...d-highest-positive-number-above-0-i4-i20.html

 

[Marcelo Branco]

More robust

=IF(COUNTIF(I4:I20,">0"),MAX(I4:I20),"Not available")
=IF(COUNTIF(I4:I20,"<0"),MIN(I4:I20),"Not available")

M.

 

[PhilipeC]

It worked thanks, i am learning excel quite a lot with the answers.