Help with Min formula

[Guy Wates]

HI

I have a range of cells with values including zero but I need the minimum value, but as some of the cells have zero I keep getting zero. How can I write a formula that gives the Minimum that is the next value above zero?

Any help appreciated.

G Wates

[richardreye]

using the morefunc addin from http://longre.free.fr/english/, I came up with this and the MMIN() function.

=SUM(MMIN(Range,COUNTIF(range,0)+1))

This doesn't work for negative numbers but you didn't mention that it had to. It ignores blanks so they are okay.

Cheers!

[anilsolipuram]

Thanks Ola Sandstrom


I am considering the range to be A1:G1

=min(if(A1:G1>0,A1:G1,"NONE"))


It should work if it's Entered as an Array formula - Hold down Ctrl and Shift then hit Enter to confirm the formula. Otherwise you will get #VALUE!

[richardreye]

I've since discovered a better Function than the one used in my previous post called SMALL(). Use like this

=SMALL(Range,COUNTIF(Range,0)+1)

anilsolipuram solution is just as good so you choose!

Cheers!

[Guy Wates]

Hi thanks for the reply but I can't get it to work so I enclose the formula, any help appreciated.

=MIN(K7:K8,L7:L8,P7:P8,Q7:Q8)

[Quinn]

Guy,

I know this is by no means elegant, but I hope it will suffice for you...

=MIN(MIN(IF(K7:K8>0,K7:K8,"NONE")),MIN(IF(P7:P8>0,L7:L8,"NONE")),MIN(IF(P7:P8>0,P7:P8,"NONE")),MIN(IF(Q7:Q8>0,Q7:Q8,"NONE")))

Best regards,

[olasa]

This should do it:
=MIN(IF(K7:L8>0,K7:L8),IF(P7:Q8>0,P7:Q8))

Ola Sandstrom


Note:
Confirm the formula by holding down Ctrl and Shift, and then press Enter.
Othervise the result will be #VALUE!