Column look up first value > 0

[Legacy 327601]

Hi,

I am trying to build a formula that will look up the first value greater than 0 in a column. This value may be in any row within this column. Also, the column will be formula driven. I am really at a loss as to where to start. This value will also be the smallest in the column yet MIN(IF( seemingly will not work for cells that are formula driven. Thank you for your help.

 

[cyrilbrd]

Hi, try either:

Excel 2013
	A	B	C
1	data		results
2	3		3
3	30		3
4	76
5	0
6	65
7	12
8	31
9	35
10	45
11	19
12	50
trackstar2k2

Cell Formulas
Range	Formula
C2	{=SMALL(IF($A$1:$A$12<>0, $A$1:$A$12, ""), 1)}
C3	{=MIN(IF(A2:A12>0,A2:A12))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Legacy 327601]

Neither of these work unfortunately.

Are the numbers in your column A formula driven?

 

[cyrilbrd]

Yes they are, may I ask what kind of formula you us are what do both formulae return?

 

[cyrilbrd]

In other words are you sure that you data are numbers or text values? Text values would make both proposed formulae inefficient.

 

[Legacy 327601]

When I do the MIN(IF( formula it returns 0. when I use the SMALL(IF( formula it returns #NUM!

I am not sure what you mean in the comment above that. Thanks

 

[Legacy 327601]

Also, the column is general - not text

 

[cyrilbrd]

Also, the column is general - not text

Please in a cell type =ISNUMBER(A1) change cell reference to refer to cell where your formula is. It should return TRUE.

 

[cyrilbrd]

When I do the MIN(IF( formula it returns 0. when I use the SMALL(IF( formula it returns #NUM!

I am not sure what you mean in the comment above that. Thanks

It means that your data are not numbers, please use the test ISNUMBER as suggested, it might return FALSE...

 

[immortal_ankur]

just want to know what is benefit of using arrey formula here. how is it different from the =small(range,1)