Identify closest date in the past on multiple criteria

[jacquesB]

Hi everyone

I am trying to count some averages but I need to create a dummy for some customer visits that should not count in the average.

I want to mark the entries where the closest date in the past where we have visited a customer on a day 6 or 7 in the past with a "No" in column E.
For example customer 1 that was visited on day 7 (March 5th) should be marked with a "No" on March 1st.

I hope that someone can see a solution.

Thank you

	A	B	C	D	E
1	Customer ID	Visit date	Day	Time spent	Should count in average?
2	5	2. marts 2017	4	0,4	Yes
3	5	6. marts 2017	1	0,8	Yes
4	5	5. marts 2017	7	0,4	Yes
5	4	3. marts 2017	5	0,6	No
6	4	2. marts 2017	4	0,3	Yes
7	4	6. marts 2017	1	0,6	Yes
8	4	10. marts 2017	5	0,5	Yes
9	3	1. marts 2017	3	0,1	Yes
10	3	9. marts 2017	4	0,7	Yes
11	2	3. marts 2017	5	0,1	Yes
12	2	2. marts 2017	4	0,9	Yes
13	2	3. marts 2017	5	0,2	Yes
14	2	5. marts 2017	7	0,0	No
15	1	5. marts 2017	7	0,0	No
16	1	1. marts 2017	3	0,8	No
17	1	3. marts 2017	5	0,6	Yes
18	1	4. marts 2017	6	0,0	No
19	1	11. marts 2017	6	0,0	No
20	1	6. marts 2017	1	0,2	Yes
21	1	8. marts 2017	3	0,2	No

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Ark1​