Min of number above zero using name a range

[praveen_ce]

HI ppl

i know this question has been asked many times but my problem is bit different

i have different values at different places say at "a1 c1 n1 "

and i have to find minimum and above zero

so i took these three cells and made a array using NAME A RANGE(named "xyz")

so now wen i use

'=min(if(xyz>0,xyz))'

its not working


plz help me

 

[anupamtiwari05]

Hi Praveen,


Just tweak your formula like below:


=IF(MIN(xyz)>0,MIN(xyz),"Min value is 0 only")


Hope this will work for you.


Thanks & Regards,

Anupam Tiwari

 

[sdsurzh]

Hi,


I am not sure about your request you try the below formulas.


=MIN(xyz)

=IF(MIN(xyz)>0,MIN(xyz)," ")


Thanks,

Suresh Kumar S

 

[praveen_ce]

thank u

but if zero is there in range

ex: "0,1,2,3" is there then i want formula to give value 1

i used

=IF(MIN(xyz)=0,SMALL(xyz,2))

but want to understand why its giving error

 

[Krishnakumar]

Hi


You may try this


=IF(MIN(xyz)=0,SMALL(xyz,2),MIN(xyz))

 

[anupamtiwari05]

Hi Praveen,


You can try below formula which is working perfectly for me:


=IF(MIN(xyz)>0,MIN(xyz),SMALL(xyz,2))


Thanks & Regards,

Anupam Tiwari

 

[Sajan]

@praveen_ce,

You were on the right path with your original formula. However, since you are generating an array with the IF statement, you just need to enter the formula with Ctrl + Shift + Enter.


=MIN(IF(xyz,xyz))


Cheers,

Sajan.

 

[Haseeb A]

Hello Praveen,


If a range is in noncontinuous, in your example A1, C1, N1 Array formula will not work. Try this;


=LARGE(xyz,INDEX(FREQUENCY(xyz,0),2))


Or,


=SMALL(xyz,INDEX(FREQUENCY(xyz,0),1)+1)

 

[Sajan]

Hi Haseeb,

Very interesting observation about non-contiguous ranges. I learned something new!

(And I clearly missed that requirement in the OP's post.)


By the way, depending on how the non-contiguous range is created, the simple MIN function should work.


For example, if the xyz range was constructed using a formula similar to the following:

=CHOOSE({1,2,3},$H$7,$J$7,$L$7)


or


=CHOOSE({1,2,3},colX,colY,colZ)


Then, OP's original formula would also work as expected:

=MIN(IF(xyz,xyz))


However, if the range is contructed using the union operator, it requires special handling as you pointed out.


Regards,

Sajan.

 

[Haseeb A]

Yes Sajan. Agreed.

 

[NARAYANK991]

Hi Haseeb ,


Yet another gem from a true Excel Ninja ! Incredible.


Narayan

 

[praveen_ce]

@sajan : good one sajan it workd

 

[Sajan]

Thanks for the feedback Praveen. I am glad it helped.

 

[niting]

Hui,


What about formula forensic on the solution posted by Haseeb and Sajan???


Thanks

Nitin