frequency

**ccruz** · 02-15-2013, 04:53 AM

Hello
is it possible have one alert or .. if and when show up mor than 2 consecutives values graeter than 6 ?

6.584
5.994
5.781
5.829
5.901
5.970
6.313
6.328
6.302

**Fotis1991** · 02-15-2013, 04:59 AM

So in your example(Assuming that you start in A1), ALL your consecutives values are greater than 6?

**benishiryo** · 02-15-2013, 05:41 AM

hi ccruz. do you need that alert to stop user from keying the 3rd consecutive value or simply state there's such instance? for the former, consider Data Validation if you can fix a range.
select the cells you want the dropdown applied to (say F1:F10). go to Data -> Data Validation -> Allow: Custom -> Formula:
=SUMPRODUCT((A1:A10>6)*(A2:A11>6))+1>=3
play around with the Error tab to show the error message

for the latter, you can use this formula:
=IF(SUMPRODUCT((A1:A10>6)*(A2:A11>6))+1>=3,"There are 3 or more consecutive values greater than 6","")

attaching an eg for you. D1 will show that message only when there are 3 or more consecutive values greater than 6. in F1:F10, you cannot key consecutive values greater than 6