Find Two Most Right Non-Null Numbers and Compare

[ptrively]

Hello!

I have a series of 5 columns, H1-L1, in those columns there can be 0 to 5 values. What I'd like to do, is compare the two furthest right values to a constant

Example:

Constant = 15

H1--I1--J1--K1--L1
10------20-------30

In this, example, I would compare 20 and 30 to my constant, both of these are greater than 15, so I would return True.

I'm POSITIVE I need to use an array, but for the life of me I can't figure out how to get the second number.

Any help is greatly appreciated.

Sincerely,
Paul

 

[dreid1011]

Give this a try:

Change U1 to wherever your constant is located.

=IF(AND(INDEX(H1:L1,,LARGE(IF(ISNUMBER(H1:L1),COLUMN(H1:L1)-7,0),1))>U1,INDEX(H1:L1,,LARGE(IF(ISNUMBER(H1:L1),COLUMN(H1:L1)-7,0),2))>U1),TRUE,FALSE)

Entered with CTRL+SHIFT+ENTER, not just ENTER.

Hopefully someone else may come by with something a bit more elegant.

 

[ptrively]

More elegant not required! I really appreciate your help with this, I'm dissecting it to see where my array formula went wrong! I really appreciate the time you took to respond!