How to find the min number in a range?

[smerrick]

How to find the min number in a range?

In column A I have a mixture of numbers and blank cells. What I need to do is to use a formula in column B that will tell me the min number in between those cells that have a number.

For example:

If Result (Min)

A1 5 B1 0
A2 Blank B2 3
A3 Blank B3 3
A4 3 B4 0
A5 Blank B5 2
A6 Blank B6 2
A7 Blank B7 2
A8 2 B8 0
A9 Blank B9 2
A10 7 B10 0


All that it is doing is to showing the user what the minimum number is between those cells that contain a number. Any help will be greatly appreciated.

Thanks

 

[Andrew Poulsom]

Please post the result(s) you would expect from your sample data.

 

[smerrick]

Hello,

I have tried to do that in my example above e.g. if cell A1 was 5 then the result in B1 would be zero. If cell A2 was blank then the result in B2 would be 3, and so on. For some reason it squashed everything up.

Thanks for looking

 

[Andrew Poulsom]

I see. Try in B1 copied down:

=IF(ISNUMBER(A1),0,MIN(LOOKUP(2,1/A$1:A1,A$1:A1),INDEX(A1:A$10,MATCH(TRUE,INDEX(ISNUMBER(A1:A$10),),FALSE))))

 

[smerrick]

That's a perfect response, why thank you - if only I knew half what you know I could then be an 'expert'.