# SMALL FUNCTION excluding blank cells

• I am trying to find the smallest number other than 0 for each day of the week using
=SMALL(IF(WEEKDAY(Table3[Date],1)*ISNUMBER(Table3[Date])=1,Table3[[Early AM]:[Bed Time]],""),1+COUNTIF(Table3[[Early AM]:[Bed Time]],0))

How can I change the formula so it does not return 0 for blank cells?

Thanks

• Re: SMALL FUNCTION excluding blank cells

A sample part of your file will be certainly helpful.
Just to prepare the right formula

Triumph without peril brings no glory: Just try

• Re: SMALL FUNCTION excluding blank cells

Where is your formula in the example sent

Triumph without peril brings no glory: Just try

• Re: SMALL FUNCTION excluding blank cells

Try this "array formula" in AL92

=SMALL(IF(TEXT(Table1[Date],"dddd")=Z92,IF(Table1[Date]<>"",IF(Table1[[Early AM]:[Bed Time]]>0,Table1[[Early AM]:[Bed Time]]))),1)

confirm with CTRL+SHIFT+ENTER and copy down to AL98 to get the minimum non-zero (and non-blank) value for each day. Note you need to correct the spelling of "WEDNESDAY" in Z95.........

• Re: SMALL FUNCTION excluding blank cells

Thanks a lot for the formula, it works really well.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!