Array formula to return value if greater than 0.5

[jblonde002]

I have inherited a spreadsheet with this array formula:
=IFERROR(INDEX(In.Out!BO:BO,SMALL(IF(In.Out!BT:BT=1,ROW(In.Out!BO:BO)-MIN(ROW(In.Out!BO:BO))+1),ROWS(ProgAll!$P$5:P5))),"")

I don't know a huge amount about Excel but the formula needs to return values greater than 0.5, not just equal to 1. Can that be done? Many thanks for any help you can give - I am well out of my depth on this!

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=IFERROR(INDEX(In.Out!BO:BO,SMALL(IF(In.Out!BT:BT>0.5,ROW(In.Out!BO:BO)-MIN(ROW(In.Out!BO:BO))+1),ROWS(ProgAll!$P$5:P5))),"")

which will return values from the BO column when BT column values are larger than 0.5.

By the way, try to restrict the ranges instead of referencing whoe columns for reasons of efficiency.

 

[jblonde002]

=IFERROR(INDEX(In.Out!BO:BO,SMALL(IF(In.Out!BT:BT>0.5,ROW(In.Out!BO:BO)-MIN(ROW(In.Out!BO:BO))+1),ROWS(ProgAll!$P$5:P5))),"")

Thanks for this. The issue was that the In.Out!BO:BO had an =IFNA formula that ended "". For some reason it counted those values as greater than 0.5 (not sure how). Your formula worked perfectly after I changed this!

 

[Aladin Akyurek]

Thanks for this. The issue was that the In.Out!BO:BO had an =IFNA formula that ended "". For some reason it counted those values as greater than 0.5 (not sure how). Your formula worked perfectly after I changed this!

A text value is always greater than a number, i.e., text > number --> TRUE. In such a situation, we can also an ISNUMBER test...

=IFERROR(INDEX(In.Out!BO:BO,SMALL(IF(ISNUMBER(In.Out!BT:BT),IF(In.Out!BT:BT>0.5,ROW(In.Out!BO:BO)-MIN(ROW(In.Out!BO:BO))+1)),ROWS(ProgAll!$P$5:P5))),"")

 

[jblonde002]

A text value is always greater than a number, i.e., text > number --> TRUE. In such a situation, we can also an ISNUMBER test...

=IFERROR(INDEX(In.Out!BO:BO,SMALL(IF(ISNUMBER(In.Out!BT:BT),IF(In.Out!BT:BT>0.5,ROW(In.Out!BO:BO)-MIN(ROW(In.Out!BO:BO))+1)),ROWS(ProgAll!$P$5:P5))),"")

Not sure I have ever learnt so much from two posts in my life! Thanks so much - wisdom received!

 

[Aladin Akyurek]

Not sure I have ever learnt so much from two posts in my life! Thanks so much - wisdom received!

You are welcome. Glad to be of help.