Counting Down a Column

[andrewc]

Hi,

I have values in column A and would like to create a formula in B1 that will find the smallest value in A and then count down the column the number of cells until a zero is reached.

E.g. if the smallest value is in A102 and then next zero is in A112, the formula should return 10.

Could someone please suggest a solution.

Thanks!

[Fotis1991]

Andrew, does this ARRAY formula works for you?

Please Login or Register  to view this content.

Confirm using Contr+Shift+Enter.

[bmouse]

If the answer is in a single cell, how would you know what the smallest value is?
Any value you'll get with a formula is meaningless, because 2 values should be given to make sense: a) the smallest value, b) the values distance to the next "0".

[andrewc]

Thanks very much, that works well

[bmouse]

Just wanted to point out that the formula provided also counts upwards, because I keep getting negative values.
Also, if there is a "0" before the smallest value, the formula ignores any zeroes down the column. Is that ok?