Smallest positive value

D

danpt

Can someone help me with a formula that will return the smallest positive
value in a given unsorted range?
Thanks
 
D

Dave Peterson

=if(countif(a1:a10,">"&0)=0,"no value",min(if(a1:a10>0,a1:a10)))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.
 
D

danpt

Hi, Dave
Great help again.
Many thanks

Dave Peterson said:
=if(countif(a1:a10,">"&0)=0,"no value",min(if(a1:a10>0,a1:a10)))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.
Click to expand...
 

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

Goal Seeking via Excel 0
1st smallest, 2nd smallest,3rd smallest 8
smallest to largest 8
Find the smallest amount 1
Finding multiple minimums??? 5
display Column Header based on cell value in a range 2
Compare values in columns and highlight the smallest value 1
adding 4 smallest numbers greater than zero 3

Top