Inquiry

[Duwin]

I have a bank of data that is updated monthly. I need to analyze said data to determine first all values above 35 then I need to look for two consecutive instances of this event. Once found, I would like to indicate for inspection if not ok

 

[Michael M]

You may need to provide more info, but have a look at Autofilter for your "above 35"....that might also give you your consecutive instances

 

[Duwin]

Thanks michael..can i do it in if functions?

 

[Peter_SSs]

Welcome to the MrExcel board!

..can i do it in if functions?

Probably, but we don't know what your data is like, where it is or exactly what you need. Could you give us a small set of dummy data and explain in realtion to that?
My signature block below has help on how to do that.

 

[Duwin]

Thanks peter..

For example i have values from cell
A1 15
A2 35
A3 50
A4 20
A5 10
A6 55
A7 70
A8 65
A9 50
A10 35

In A11 the logic must be when there is a consecutive cell with values equal or greater than 35..it must say "for inspection" ..if none it must say "okay"

 

[Peter_SSs]

In A11 the logic must be when there is a consecutive cell with values equal or greater than 35..it must say "for inspection" ..if none it must say "okay"

Okay, try this. I've given an example of each result.
This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

00 HTML Conversions.xlsm
	A	B
1	15	15
2	35	25
3	50	39
4	20	30
5	10	25
6	55	88
7	70	4
8	65	16
9	50	32
10	35	37
11	for inspection	okay
Duwin

Cell Formulas
Range	Formula
A11	A11	=IF(MAX(FREQUENCY(IF(A1:A10>35,ROW(A1:A10)),IF(A1:A10<=35,ROW(A1:A10))))>1,"for inspection","okay")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Duwin]

Thanks peter..it is a big help ?

 

[Peter_SSs]

Thanks peter..it is a big help ��

You're welcome. Thanks for the follow-up.

 

[Duwin]

Follow up question

Given same data

In a11 the logic must be when there is a cell with greater or equal to 35 and the consecutive cells are not yet zero, it must say "inspect" if not "ok"

 

[Peter_SSs]

Follow up question

Given same data

In a11 the logic must be when there is a cell with greater or equal to 35 and the consecutive cells are not yet zero, it must say "inspect" if not "ok"

If we have the same data how is the requirement different?

What do you mean by "consecutive cells are not yet zero"?

Perhaps we do need a couple more sets of sample data and the results?