Find smallest +Ve number from a range containing +Ve and -Ve values

[awagdarikar]

Greetings,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
This is Aniruddha and I am a new member of this site. I need to find the smallest positive value from a range A1:D1 (containing both positive and negative values) as illustrated below.<o></o>
<o> </o>
Cell A1: 176.12 Cell B1: -26.12 Cell C1:3.88 Cell D1: 363.88<o></o>
<o> </o>
Obvious answer is Cell C1: 3.88 I have tried hard but I am not able to set a formula for this issue.<o></o>
<o> </o>
Thank you,<o></o>
<o> </o>
Aniruddha<o></o>

 

[erik.van.geit]

Hi, awagdarikar,
WELCOME to the BOARD!!!!!

This works for me
=SMALL(A1:D1,COUNTIF(A1:D1,"<0")+1)

kind regards,
Erik

 

[dafan]

Confirm this with Ctrl-Shift-Enter, not just Enter!

=MIN(IF(A1:D1>0,A1:D1))

HTH.

 

[awagdarikar]

Hi erik.van.geit,

Thanks for prompt responce but I get 176.12 as answer when I should be getting 3.88 as the answer.

Please help,
Aniruddha

 

[awagdarikar]

ADVERTISEMENT

Thanks defan,

but this gives #Value! error,

Thanks,
Aniruddha

 

[dafan]

As I said, type in the formula, then confirm with Control+Shift+Enter, NOT just Enter.

 

[awagdarikar]

ADVERTISEMENT

Hi Eric,
Thanks for the reply. i had applied it in a wrong way, now I corrected it.
Your formula works well & you are rocking,
Thanks again,
Aniruddha

 

[awagdarikar]

Confirm this with Ctrl-Shift-Enter, not just Enter!

=MIN(IF(A1:D1>0,A1:D1))

HTH.

Yes defan,

It works after the array thing.

Thanks again,
Aniruddha

 

[dafan]

No worries, learned something new today and that's what counts