counting non-blanks

[skhou]

Hi guys,

I've got the following situation, which I'm wondering whether I can solve using a formula, rather then coding it.

I have a the following list of values:

0,0,0,3,6,0,8,0,0,1

What I want to do is be able to choose the value in the lost that I want to pull out which is not Zero. e.g Is there a way of being able to pull out say the first value which is not zero being a "3" or the 2nd value in the list which is not zero being a "6", or the third value in the list which is not zero being a "8"?

thanks,

Sonny

 

[Aladin Akyurek]

Let A2:A11 house the data and C2 a value like 2, meaning get the 2nd greater than 0 value.

Control+shift+enter, not just enter...

=INDEX($A$2:$A$11,SMALL(IF(ISNUMBER($A$2:$A$11),IF($A$2:$A$11>0,ROW($A$2:$A$11)-ROW($A$2)+1)),C2))

 

[skhou]

Aladin - That works. Thanks heaps !

 

[skhou]

Aladin - Just for my own understanding, are you able to explain the logic in this formula?

thanks

 

[Aladin Akyurek]

Aladin - Just for my own understanding, are you able to explain the logic in this formula?

thanks

1) Lets p stand for the position at which the Nth greater than 0 numeric value is located, thus:

INDEX($A$2:$A$11, p)

If we can calculate p, INDEX will return the value at pth cell in A2:A11.

2) First we can calculate the set of greater than 0 numeric values with:

IF(ISNUMBER($A$2:$A$11),IF($A$2:$A$11 > 0, x))

2a) If we equate x to $A$2:$A$11, we would get something like

{FALSE;FALSE;FALSE;3;6;FALSE;8;FALSE;FALSE;1}

where FALSE corresponds to cells that are either empty or housing numeric values less than or equal to 0.

2b) If we equate x to ROW($A$2:$A$11)-ROW($A$2)+1, we would get something like

{FALSE;FALSE;FALSE;4;5;FALSE;7;FALSE;FALSE;10}

where the integers are the positions which correspond to cells housing greater than 0 numeric values. What FALSE means is easy to derive by now.

Note that

ROW($A$2:$A$11)-ROW($A$2)+1

is just

{1;2;3;4;5;6;7;8;9;10}

which is a sequential numbering of the celss of A2:A11.

3) If we apply SMALL to x which holds positions like in

SMALL(x,N) == p

where N is given in C2, we are giving INDEX what it needs to return the desired result.

 

[skhou]

Aladin - Understood. Thankyou for the very thorough explanation. Fantastic Stuff!

cheers,

Skhou