How to get range of dynamic column?

QuestionEdit

This is a follow up to a question I asked here last week. I need to get the lowest value of odd numbered rows only, so I'm using the code you suggested:

=SMALL((IF(MOD(ROW(B1:B8),2)=1,B1:B8,9999999)),2)

That's working great, thanks.

My new problem is that I'm using dynamic data, so the range changes each time. It varies from B1:B8 up to B1:36. If the range is less than 36, the formula returns zero as the lowest value.

How can I get the formula to ignore the empty cells?

AnswerEdit

I assume that a zero value wouldn¡¯t be allowed anyway so in that situation

=SMALL((IF(MOD(ROW(B1:B36),2)=1,IF(B1:B36>0,B1:B36,9999999),9999999)),2)

would resolve it ¨C if a zero IS valid then

=SMALL((IF(MOD(ROW(B1:B36),2)=1,IF(NOT(ISBLANK(B1:B36)),B1:B36,9999999),9999999)),2)

would do it