How do I get "minimum value" in a range to NOT return zero?

B

Blade

I'm trying to keep the =MIN from returning 0 as the minimum value. Is this
possible?
 
J

JulieD

Hi Blade

try
=SMALL(C1:C10,COUNTIF(C1:C10,0)+1)

where C1:C10 is the range you're looking for the minimum in.

Cheers
JulieD
 
P

Peo Sjoblom

If there are only positive values

=LARGE(A1:A10,COUNTIF(A1:A10,">0"))

if there can be negative values

=MIN(IF(A1:A10<>0,A1:A10))

note that the latter formula MUST be entered with ctrl + shift & enter

Regards,

Peo Sjoblom
 
B

Blade

Thanks, this works great when selecting a range like A1:A:10
Is there different syntax for selecting individual cells like A1, A3, A5, A7
where A1, A3, A5, A7 represent values for a separate calculation?
 
M

Myrna Larson

Here's one way:

=MIN(IF(A1:A7<>0,IF(MOD(ROW(A1:A7),2)=1,A1:A7)))

This is an array formula and must be entered with CTRL+SHIFT+ENTER.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

HLOOKUP, LOOKUP & MATCH doesn't work. Seems not right. 1
Need to get minimum difference 1
Help needed: Minimum value based upon either/or criteria 3
Divide by the minimum non-zero number in a range of cells 22
Newbie Help - IF Yes No Function 0
How do I find the minimum value in a range while ignoring zeros? 4
Trying to find additional ranges outside of min, max, and average 2
Minimum without zeros or negatives 3

Top