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]

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

Because you add one (1) condition that is "different from zero" hence array required.

 

[Aladin Akyurek]

@trackstar2k2

You seem to have a problem with your data, created by some formula you invoke.

Care to post that formula which creates the data?

Also, would you check the following?

=MIN(IF(ISNUMBER(1/$A$2:$A$10),IF($A$2:$A$10+0>0,$A$2:$A$10+0)))

which must be confirmed with control+shift+enter, not just enter.

 

[Legacy 327601]

Hi - sorry for the delay. Yes, it does return false

 

[Legacy 327601]

@trackstar2k2

You seem to have a problem with your data, created by some formula you invoke.

Care to post that formula which creates the data?

Also, would you check the following?

=MIN(IF(ISNUMBER(1/$A$2:$A$10),IF($A$2:$A$10+0>0,$A$2:$A$10+0)))

which must be confirmed with control+shift+enter, not just enter.

The formula is =IF(ISNUMBER(FIND("Q4",F32)),LEFT(F32,4),"")

The goal of the formula is to return the year(hence the =LEFT(F32, 4) formula)

the F column is under the accounting format and it is hardcoded

 

[Legacy 327601]

Also, would you check the following?

=MIN(IF(ISNUMBER(1/$A$2:$A$10),IF($A$2:$A$10+0>0,$A$2:$A$10+0)))

which must be confirmed with control+shift+enter, not just enter.

This formula works. Thank you!

 

[Aladin Akyurek]

The formula is =IF(ISNUMBER(FIND("Q4",F32)),LEFT(F32,4),"")

The goal of the formula is to return the year(hence the =LEFT(F32, 4) formula)

the F column is under the accounting format and it is hardcoded

You could have invoked:

=IF(ISNUMBER(FIND("Q4",F32)),LEFT(F32,4)+0,"")

in order to get a true number/year.

This formula works. Thank you!

You are welcome.