Return a value based on cells above the referenced cell

[rlhchung]

Hi All,

Trying to wrap my head around creating a formula for the cell to return values based on the values above a referenced cell. Example I'm trying to make Column C return "Y"s for any value that is equal or less than 10 in column B, and if it is higher than 10 that it will return "N" for 4 rows after that. E.g.

B2 = 13 so, C2 = N

^ value above 10

B3 = 0 so, C3 = N

^ 1st row after a value above 10

B4 = 0 so, C4 = N

^ 2nd row after a value above 10

B5 = 0 so, C5 = N

^ 3rd row after a value above 10

B6 = 0 so, C6 = N

^ 4th row after a value above 10

B7 = 0 so, C7 = Y

^ after the 4th row where the referenced cell was higher than 10 return value is Y

B8 = 0 so, C8 = Y

^ after the 4th row where the referenced cell was higher than 10 return value is Y

B9 = 0 so, C9 = Y

^ after the 4th row where the referenced cell was higher than 10 return value is Y

B10 = 0 so, C10 = Y

^ after the 4th row where the referenced cell was higher than 10 return value is Y

B11 = 0 so, C11 = Y

^ after the 4th row where the referenced cell was higher than 10 return value is Y

Has anyone created a query like this? Please help. I also attached the workbook as to which I'm working on.

Thanks,

Raymond

[Croweater]

Here's a quick and easy way using D as a helper column

In D2 and drag down
=IF(B2>10,1,IF(C2=C1,D1+1,1))

In C2
=IF(B2>10,"N","Y")

In C3 and drag down
=IF(AND(C2="N",D2<5),"N",IF(B3>10,"N","Y"))

[AliGW]

Welcome to the forum.

Please add 20-30 rows of expected outcomes in C and post the workbook again.

B7 = 0 so, C7 = Y

However, B7 is not 0, it's 1, so ... ?

[Glenn Kennedy]

please fill about 25 expected results ONTO your sheet, as I cannot follow the logic, especially down around rows 13-18 of your real data. Also explain why C2 is N when B2 is >10??!!