Ignore blanks when calculating smallest value

[nightracer]

Hi, I have a column that brings in results via formula from other locations in the workbook, so if there is nothing to return it gives "" (blank)
I then need to find the smallest value in this column, but because of the blanks it is returning 0 (zero).
Is there a way to get the smallest non-zero value?

For example - column A:
23
17
(Blank)
46
91
(Blank)
11
103
(Blank)
(Blank)

Then i'm hoping to get to small(a:a,1) = 11
At the moment it is returning 0

Any help appreciated
Many thanks
N

 

[arthurbr]

Perhaps

=aggregate(15,6,(a1:a100)/(a1:a100<>""),1)

 

[Jimmypop]

Hi there...

Another option could be... Not sure what version of Excel you are using though so might not work...

=SMALL(IF((A:A<>0)*(A:A<>""), A:A), 1)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Then i'm hoping to get to small(a:a,1) = 11
At the moment it is returning 0

In that case the blank cells are not blank & they do not contain "" as small will ignore them, they almost certainly contain 0

 

[nightracer]

Many thanks for the replies, as Fluff suggests it looks like one of the cells has returned a 0 which was throwing things out.
Appreciate all the help.

Thanks again
N