How could I make that simplier:
IF(AND(C$36>140;D$36>140;E$36>140);"OK";IF(AND(D$36>140;E$36>140;F$36>140);"OK";IF(AND(E$36>140;F$36>140;G$36>140);"OK";IF(AND(F$36>140;G$36>140;H$36>140);"OK";"NO"))))
This formula is in I38.
The idea is to check if in the 6 cells before (on line 36), 3 in-a-row are above 140. So the cells from C36 to H36.
I'm wondering if that is possible to make that simplier/prettier. And also so that ii is to replicate it.
The formula can be shortened with COUNTIF. Also, separate formulas make it easier to read.
Enter =COUNTIF(C36:E36;">140") in cell E37 and then drag right to fill through H37.
Set I38 to =IF(COUNTIF(E37:H37;"=3");"OK";"NO").
EDIT:
The first formula checks each set of 3 cells in a row to see how many numbers in the set are over 140. The second formula checks if any of the results are equal to 3. If so, then there is at least one set where 3 out of 3 are over 140.
Combining this into one formula would look like this:
=IF(OR(COUNTIF(C36:E36;">140")=3;COUNTIF(D36:F36;">140")=3;COUNTIF(E36:G36;">140")=3;COUNTIF(F36:H36;">140")=3);"OK";"NO")
Or this:
=IF(MAX(COUNTIF(C36:E36;">140");COUNTIF(D36:F36;">140");COUNTIF(E36:G36;">140");COUNTIF(F36:H36;">140"))=3;"OK";"NO")