I have column A and every 3 rows I want to check to see if the value "Fail" exists in there.
I have this, but I need to figure out the function to set the range to every 3 rows to check for the value:
=IF(ISNUMBER(SEARCH(A2:A4,"Fail")),"Fail","Pass")
the A2:A4 is the 3 row range that I need to check every segment (e.g. A5:A7, A8:A10). If any one of the 3 cells in that range has the word "Fail" then I need to label all three cells in the formula column "Fail"
any ideas?
>Solution :
Something like this with COUNTIFS, INDEX, and MOD:
=IF(COUNTIFS(INDEX(A:A,ROW()-MOD(ROW()+1,3)):INDEX(A:A,ROW()+2-MOD(ROW()+1,3)),"Fail"),"Fail","Pass")
