My array formula is counting blanks as zero - how do I differentiate?

[lincolnshep]

Hi,

I'm trying to find the row number of the last cell in an array that is equal to zero but is not blank. My array is one column wide and 38 rows deep (K6:K43). The top 1 to x rows will always have a value (0, 3, 5 or 7) and the bottom x+1 to 38 rows will always be blank.

I've tried:
{=MAX(IF(K6:K43=0,ROW(K6:K43)))} : this returns 43 (which suggests it's counting the blanks as zero)
{=MAX(IF(AND(K6:K43=0,K6:K43<>""),ROW(K6:K43)))} : this returns 0

I'm obviously missing something (I'm quite new to array formulae) so any help would be greatly appreciated.

Thanks and regards,
Shep

 

[XOR LX]

Hi.

=MAX(IF(+K6:K43=0,ROW(K6:K43)))

Regards

 

[lincolnshep]

Hello XOR LX,

{=MAX(IF(+K6:K43=0,ROW(K6:K43)))} is also returning 43 (i.e. the last blank in the range, rather than the last zero in the range) but, for my own interest, what does adding the "+" do to the formula?

Thanks,
Shep

 

[Peter_SSs]

I'm trying to find the row number of the last cell in an array that is equal to zero but is not blank. My array is one column wide and 38 rows deep (K6:K43). The top 1 to x rows will always have a value (0, 3, 5 or 7) and the bottom x+1 to 38 rows will always be blank.

This?

=5+COUNT(K6:K43)

 

[XOR LX]

{=MAX(IF(+K6:K43=0,ROW(K6:K43)))} is also returning 43 (i.e. the last blank in the range, rather than the last zero in the range)

Apologies. I made the assumption that your "blanks" were actually null strings, i.e. "".

Use:

=MAX(IF(ISNUMBER(K6:K43),IF(K6:K43=0,ROW(K6:K43))))

which will work no matter whether the blanks are "genuine" blanks or the null string "".

Regards

 

[lincolnshep]

This?

=5+COUNT(K6:K43)

Hi Peter,
I'm looking for cells that equal zero, COUNT will give me all cells with numbers regardless of value.
Thanks,
Shep

 

[lincolnshep]

=MAX(IF(ISNUMBER(K6:K43),IF(K6:K43=0,ROW(K6:K43))))

You've nailed it! Wonderful.

Thank you very much for your help - this has been doing my head in, I was focussed on AND and didn't consider a nested IF.

Kind regards,
Shep

 

[Peter_SSs]

Hi Peter,
I'm looking for cells that equal zero, COUNT will give me all cells with numbers regardless of value.
Thanks,
Shep

Yes, sorry, I misinterpreted your question.

If using Excel 2010 or later you could also try this non array-entered formula.

=AGGREGATE(14,6,ROW(K6:K43)/(0&K6:K43="00"),1)

 

[lincolnshep]

Yes, sorry, I misinterpreted your question.

If using Excel 2010 or later you could also try this non array-entered formula.

=AGGREGATE(14,6,ROW(K6:K43)/(0&K6:K43="00"),1)

Thank you Peter. I have not used AGGREGATE before, I will enjoy picking this apart to see what it does!

Shep

 

[Peter_SSs]

Thank you Peter. I have not used AGGREGATE before, I will enjoy picking this apart to see what it does!

Shep

Glad to help.

FWIW a combination of mine & XOR LX's would give another quite short (array) formula for use in all versions:

{=MAX(ROW(K6:K43)*(0&K6:K43="00"))}