Find Smallest Number without including blank cells

[ssminnow]

I am trying to find the smallest number(including negative numbers and zero) but not including blank cells. I currently have
=SMALL($AV11:$AV114,1) which lists the first smallest number. I also have =SMALL($AV11:$AV114,2) which lists the second smallest number.
I have a few empty cells in (AV11:AV114)and it is not giving me a result. Any help would be appreciated. Thanks.

[CK76]

Can you upload sample? SMALL function should ignore blank cells mixed in range, without any help.

0.JPG

[FDibbins]

Try this ARRAY formula...
=SMALL(if($AV11:$AV114<>"",$AV11:$AV114),1)

[FDibbins]

Can you upload sample? SMALL function should ignore blank cells mixed in range, without any help.

Good point.

ssminnow, do the "blank" cells contain formulas?

[ssminnow]

Yes, the blank cells contain formulas.

[FDibbins]

OK, then my suggestion should work - the cells maybe blank, but they are not empty

[CK76]

Hmm, but SMALL does ignore formula generated blanks & texts. Only issue would be when range contains error values.

[FDibbins]

Hmm, but SMALL does ignore formula generated blanks & texts. Only issue would be when range contains error values.

I must have left my brain in bed when I got up this morning - that is also true.

I guess we really do need to see a sample of what you are working with, so we can see what the problem is

[ssminnow]

Sorry to be so slow but how do you upload a sample on to this site.

[CK76]

I must have left my brain in bed when I got up this morning

I know the feeling all too well I need 2 cups of coffee before I start functioning.

To upload a file, use "Go Advanced" button found at bottom right of EDIT/Quick Reply screen. Then click on "Manage Attachments" hyperlink.
It will launch new tab/window.

[ssminnow]

Here is the attachment. I am looking to fix the formula in Q132, Q133, Q134

[ssminnow]

Something is now quite right. Now I get a #Value Error.

[ssminnow]

I figured it out now. It is an array.

Thanks for the help.

[CK76]

Ah, that's because you are not using blanks but 0 displayed as blanks in the workbook.

Since, 0 is the lowest value in the column and also there are many more 0s... you get 0.

As well, AV range has formula...

Please Login or Register  to view this content.

Instead, change to...

Please Login or Register  to view this content.

Or alternately change formula for SMALL to...

Please Login or Register  to view this content.

[FDibbins]

Here is the attachment. I am looking to fix the formula in Q132, Q133, Q134

Unless you deleted the attachment, it didnt come through?

[CK76]

Looks like he did delete it. Op had "Show a zero in cells that have zero value" unchecked in the sheet option.

[FDibbins]

OK thanks