Next Excel question

[one cool dog]

I am moving along nicely and getting to grips with things, but this one is bugging the hell out of me.

I have a column range of say 12 cells. Of those 12 cells I may only use 6 to produce a number, the other 6 will stay blank ( I have unticked zero values in options)

I want the cell below my range to show me the lowest number, so that can be part of my next formula.

If I use the MIN formula, it always shows 0 because, logically, 0 is the lowest number. However, I want the lowest number that is greater than zero to be shown in the cell.

What formula will produce the lowest number great than zero in a cell rage eg.


L
1 12
2 0
3 45
4 17
5 0
6 0
7 23
8 0
9 12
10 56
11 9
12 0

13

I want L13 to equal 9 and not 0 using a MIN formula, or something else that will work. The formula should be dynamic as the number values will change above L13 quite a lot.

Cheers for advice.

[GaryRussell]

There may be an easier way, but you can do this with a custom function.

1) Press alt-f11 to bring up the code editor.
2) Right click on 'Microsoft Excel objects' and choose insert|Module
3) Paste the code below into Module1 and save

You can now use the function getMinValue instead of min.

Function getMinValue(values As Range) As Integer
Min = 0
For Each n In values
If Min = 0 Then
If n.Value <> 0 Then Min = n.Value
Else
If n.Value < Min And n.Value <> 0 Then Min = n.Value
End If
Next
getMinValue = Min
End Function

[sjaak1943]

Maybe this can do the job.


SMALL Function/Formula

By far the most efficient method is to use the SMALL and COUNTIF formula as shown below;

SMALL Returns the k-th smallest value in a data set.

=SMALL(A1:A100,COUNTIF($A$1:$A$100,0)+1)

Where the countif is counting the zeros in the range (+1) and is used to tell SMALL to return the k-th smallest value.

[one cool dog]

Cheers for that. I went for the formula in this instance, as this is what I am trying to learn at the moment. I had no idea there was a small argument to use and what it could do. I will do my best now to fully understand the statement.

How difficult will it be to learn vb language?

[GeorgeUK]

You'd be amazed at how easy vba is to learn.
I'm self-taught.

Just keep breaking down what you are trying to do into small pieces rather than tackling the whole thing and you'll see how quickly you make progress.

If you need some example code to make sure you are using the correct syntax, you can try mrexcel.com or the google groups. Both are very helpful.