Condition for sequence of cells greater than pointed value

Greetings to all.

Attached Excel requires condition written for the below query (your help appreciated):
(i) Range of cells will be entered at M1 cell (here value entered as 6).
(ii) From right side, 6th cell will be pointed.
(iii) This 6th cell's value has to be LESSER THAN OR EQUAL TO each and every cell (remaining 5 cells -> (5, 4, 3, 2, 1) - from right side). If (i) to (iii) found to be good then it is considered to be 'PASS'.
(iv)
(a) If sequence of cells' range found to be < 6 OR
(b) if any of the remaining 5 cells values found to be lesser than 6th value, then it is considered to be 'FAIL'.

Thanks.

Try

in L2

=IFERROR(IF(OR(COUNT($B2:$K2)<$M$1,COUNTIFS(OFFSET($B2,0,COUNT($B2:$K2)-$M$1+1,1,$M$1-1),">"&OFFSET($B2,0,COUNT($B2:$K2)-$M$1,1,1))<$M$1-1),"FAIL","PASS"),"FAIL")

copy down

Variation on a theme:

Formula:

Please Login or Register  to view this content.

Originally Posted by TMS

Variation on a theme:

Formula:

Please Login or Register  to view this content.

Thanks so much TMS, much helpful. This is what I expected. Great effort! Added 'Reputation'. Kind regards to you all.

Originally Posted by JohnTopley

Try

in L2

=IFERROR(IF(OR(COUNT($B2:$K2)<$M$1,COUNTIFS(OFFSET($B2,0,COUNT($B2:$K2)-$M$1+1,1,$M$1-1),">"&OFFSET($B2,0,COUNT($B2:$K2)-$M$1,1,1))<$M$1-1),"FAIL","PASS"),"FAIL")

copy down

Thanks so much JohnTopley, much helpful. This is what I expected. Great effort! Added 'Reputation'. Kind regards to you all.

Below 'OR' condition does not work (row starting with fruit name Apricot). Help please.

Excel file updated and uploaded now. Row starting with Apricot

(iii) This 6th cell's value has to be LESSER THAN OR EQUAL TO each and every cell (remaining 5 cells -> (5, 4, 3, 2, 1) - from right side). If (i) to (iii) found to be good then it is considered to be 'PASS'.

Can someone kindly help, please..

Try:

Formula:

Please Login or Register  to view this content.

As opposed to:

Formula:

Please Login or Register  to view this content.

Originally Posted by JohnTopley

Try

=IFERROR(IF(OR(COUNT($B7:$H7)<$M$1,INDEX($B7:$K7,COUNT($B7:$H7)-$M$1+1)>MIN(INDEX($B7:$K7,COUNT($B7:$H7)-$M$1+2):INDEX($B7:$K7,COUNT($B7:$H7)-$M$1+6))),"fail","pass"),"fail")

Real thanks @JohnTopley for the reply. Helpful!

How to take a particular column (here 2nd column value for all rows but not the 6th cell value) and check the condition. Awaiting for the solution. Thanks in advance!

2nd column.png

Can someone kindly help for the above comment, please..