Range of values greater than particular column's values

Greetings to all.

Attached Excel needs a formula written for the below condition:

(i) Last 5 cells' values (or least value from the range of 5 cells) need to be greater than or equal to 'L Column' (specific row's value). Then said to be 'PASS'
or
Else 'FAIL' (ALSO if no values found in 'L column' then 'FAIL').

Image for reference:
Values.png

Thanks in advance.

What value should be in L8 for the comparison?

I thought this looked familiar. It's very like your request here: http://www.excelforum.com/excel-form...s-formula.html

In that thread, you keep adding requirements - have you stated your requirements in their entirety this time, or will you be changing them as the thread progresses? I am happy to try and help, as long as you have told us everything we need to know at the outset.

How about in M2:

Please Login or Register  to view this content.

Originally Posted by AliGW

I thought this looked familiar. It's very like your request here: http://www.excelforum.com/excel-form...s-formula.html

In that thread, you keep adding requirements - have you stated your requirements in their entirety this time, or will you be changing them as the thread progresses? I am happy to try and help, as long as you have told us everything we need to know at the outset.

Thanks Ali for your reply. Hope, I extend not that much this time my requirements.

Originally Posted by AliGW

What value should be in L8 for the comparison?

'L Column' cells may/may not contain values. Not mandatory though. Also, kindly check the below condition for 'L column' values comparison.

Originally Posted by LesliePrabakar

or
Else 'FAIL' (ALSO if no values found in 'L column' then 'FAIL').

If no values found in 'L column', then intentionally need 'FAIL' comment.
For instance: Row 6 (Grape row) and Row 8 (Mango row) -> FAIL

I think you have your answer, but for the record, the result in M4 of your sample data should be PASS based on your requirements as stated above.

Originally Posted by AliGW

I think you have your answer, but for the record, the result in M4 of your sample data should be PASS based on your requirements as stated above.

Thanks for your identification, Ali. Mistakenly given wrong entry. But updated with right Excel in the main thread itself now.

Have you tried #4?

Originally Posted by bebo021999

How about in M2:

Please Login or Register  to view this content.

Thanks and much helpful.

How about help on feeding the range from a separate cell where I would give the range and taken as reference in the formula.
With this I would close this thread as 'SOLVED'

Help appreciated.

Originally Posted by bebo021999

Have you tried #4?

Yes, works fine @bebo021999.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Originally Posted by LesliePrabakar

...
How about help on feeding the range from a separate cell where I would give the range and taken as reference in the formula.
With this I would close this thread as 'SOLVED'

Help appreciated.

Still looking for a help on the last query posted.

Originally Posted by AliGW

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Please help for the above query for Closure. Thanks Ali.

I won't be helping, sorry. I said that I would help if you stated all of your requirements at the start of the thread, but once again you are shifting the goal posts, just like you did before in the thread that was linked to above. I won't be led a merry dance again. Sorry!

Originally Posted by AliGW

I won't be helping, sorry. I said that I would help if you stated all of your requirements at the start of the thread, but once again you are shifting the goal posts, just like you did before in the thread that was linked to above. I won't be led a merry dance again. Sorry!

Fine @AliGW. Will try correcting myself next time onwards. Sorry to bother you as that was not my intention in prolonging a thread asking multiple queries.

Can someone here help, kindly.

Try to reupload sample with new info, Leslie

How about help on feeding the range from a separate cell where I would give the range and taken as reference in the formula.

You'll need to explain what you mean by this.

Originally Posted by bebo021999

Try to reupload sample with new info, Leslie

Yes, Excel file updated and uploaded in this reply, FYA.

Originally Posted by LesliePrabakar

...
How about help on feeding the range from a separate cell where I would give the range and taken as reference in the formula.
...

Above is the query for the updated Excel file.
Values.png

As Bebo seems to have gone offline, here's my suggestion:

IF($L2="","FAIL",IFERROR(IF(MIN(OFFSET($A2,,COUNTA($B2:$K2),,-$N2))>=$L2+0,"PASS","FAIL"),"FAIL"))

If that takes care of your new question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Originally Posted by AliGW

As Bebo seems to have gone offline, here's my suggestion:

IF($L2="","FAIL",IFERROR(IF(MIN(OFFSET($A2,,COUNTA($B2:$K2),,-$N2))>=$L2+0,"PASS","FAIL"),"FAIL"))

@AliGW, thanks so much for the solution.

Even after bugging with multiple questions, you people really helped me.

Originally Posted by AliGW

If that takes care of your new question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Closing the thread as SOLVED.

Added REPUTATION to @bebo021999 and @AliGW .

So kind of you people (admin and people here, thanks again).

You got lucky.

Thanks for the rep.