IF X = True within a Dynamic Range?

Hi All,

Need some help please...

I require a formula to achieve the below.


In cell M21:
IF L21 = "L" then
IF( > AVERAGE(B21,C21)+(B21-E21) is listed before
( < AVERAGE(B21,C21)-(B21-E21) between B21:$B$9999 then
return PASS, else return FAIL

ELSE ""

This formula would then be copied down throughout the dataset in column M

I have attached a sample dataset/worksheet

Any help would be greatly appreciated. Thank you in advanced

Hi,

Apologies for the delayed response.

I have added to the sample data set to provide a handful of expected results to hopefully better demonstrate my requirements. Whilst I have calculated them manually I need the range lookup to be dynamic as the base data will be frequently updated... all is better described in the attached (Sample Data 2.xlm)

Thank you

I'm unclear. I don't know what you mean by "...is listed before"
Also, in your attachment in the text box, you have

IF B21:$B$54 (>AVERAGE(B21,C21)+(B21-E21)

when you have "B21:$B$54" are you saying the AVERAGE of this range? So in English, is it:

If the average of the values in the cells from B21 to B54 is greater than the average of cells B21 and C21, PLUS B21 - E21, then it's a pass? (again, not sure what you mean by "is listed before"" after this part.)

You also anchor cell $B$54 but you talk about copying down the formula. Is that really what you want to do? Is it supposed to compare the range that ends before the next "L" in column L?