Find the first value in a range that is higher or lower.

[dv0x]

Hi Ninjas,

I am trying to find the first value in a range that is higher or lower than a given value and then return a corresponding value in the same row, but a different column. Whether the value sought in the range is higher or lower is dependent upon 2 scenarios. I have attached a sample to define the problem(s) more precisely.

I understand that the most practical solution may be to combine Scenario 1 and Scenario 2 in one cell and then Scenario 1 and Scenario 3 in another cell instead of trying to get it all done in one formula.

Thank you

 

[Jake Collins]

Dear dv0x

An array formula such as (this is for cell m83)

=IF(L83="OPEN","",IF(L83="CLOSED",IF(K83="DOWN",INDIRECT("A"&MIN(IF(C3:C83>H2,ROW(C3:C83),MAX(ROW(C3:C83))))),IF(K83="UP",INDIRECT("A"&MIN(IF(D3:D83<H2,ROW(D3:D83),MAX(ROW(D3:D83))))),"N/A")),"N/A"))

achieves your aims.

This can also be cut and pasted into the other m cells to generate all the desired behaviours.

 

[bines53]

Hi @Jake Collins,

This is a classic case, why not use volatile function, the guy working in the capital market, the datas change every moment, the last thing he wants to receive unnecessary calculations,
I say this from experience.

regards
David
http://chandoo.org/forum/members/jake-collins.7933/

http://chandoo.org/forum/members/jake-collins.7933/

 

[dv0x]

Thank you @Jake Collins. It appears that this solution will work fine for my purposes. I need to familiarize myself with the INDIRECT function.

Regards

 

[bines53]

Hi @dv0x,


This is what you want,

=SUMPRODUCT((K83="DOWN")*(L83="CLOSED"),AGGREGATE(15,6,A3:A83/(C3:C83>H2),1))

David
http://chandoo.org/forum/members/dv0x.23623/

 

[dv0x]

Thank you for your input @bines53. For my purposes that formula would need some modification as the range C3:C83 sometimes moves to column D. I think I could make it work by adding a IF statement.

Regards