I'm familiar only with the basics of Excel (I use Excel 2007) and am hoping someone can help point me in the right direction for this task...

We have a large set of data, with the following basic format...


-------- May 1 ----- May 8 ----- May 15 ----- May 22 ---- etc

Trap 1------- 0 ------ 2 ---------- 3 ---------- 1 ---- etc

Trap 2------- 0 ------ 4 ---------- 0 ---------- 6 ----- etc

Trap 3------- 2 ------ 1 ---------- 3 ---------- 3 ----- etc

Trap 4------ 1 ------ 2 ---------- 1 ---------- 1 ----- etc


The goal is to determine when, and where, a particular trap count threshold is reached.


We need to know when/where any given trap reaches a total of 4 or more in any two week period.

So for example, in the above data, Trap 1 would reach this threshold on May 15 (because that week, plus the previous week, have a total of 5)

Trap 2 would reach the threshold on May 8 and again on May 15, and May 22.

Trap 3 would reach the threshold on May 15, and then again on May 22.

Trap 4 would not reach the threshold.


What I would like is to have Excel count the number of times this occurs in each row, and present that number. Perhaps even colour the cell in which the threshold occurs as well?


Ideally, we would like to have Excel "ignore" 1 week after each threshold is reached, before it begins calculating again , but I'm assuming that's not possible. Eg. in Trap 2, we would like it to NOT flag a threshold on May 15 (because it was already just flagged the previous week)


I hope I have explained my needs clearly... I only know enough advanced functions of Excel to confuse myself... lol