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
Re: SMALL FUNCTION excluding blank cells
I uploaded the file.forum.ozgrid.com/index.php?attachment/38477/
Re: SMALL FUNCTION excluding blank cells
Where is your formula in the example sent
Re: SMALL FUNCTION excluding blank cells
file with the formulaforum.ozgrid.com/index.php?attachment/38479/
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.
Don’t have an account yet? Register yourself now and be a part of our community!
