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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Customer ID[/TD]
[TD="align: center"]Visit date[/TD]
[TD="align: center"]Day[/TD]
[TD="align: center"]Time spent [/TD]
[TD="align: center"]Should count in average?[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]2. marts 2017[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]0,4[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6. marts 2017[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0,8[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5. marts 2017[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]0,4[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]3. marts 2017[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]0,6[/TD]
[TD="align: center"]No[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]2. marts 2017[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]0,3[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6. marts 2017[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0,6[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]10. marts 2017[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]0,5[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1. marts 2017[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0,1[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]9. marts 2017[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]0,7[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3. marts 2017[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]0,1[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2. marts 2017[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]0,9[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3. marts 2017[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]0,2[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5. marts 2017[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]0,0[/TD]
[TD="align: center"]No[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]5. marts 2017[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]0,0[/TD]
[TD="align: center"]No[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1. marts 2017[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0,8[/TD]
[TD="align: center"]No[/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3. marts 2017[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]0,6[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4. marts 2017[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]0,0[/TD]
[TD="align: center"]No[/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]11. marts 2017[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]0,0[/TD]
[TD="align: center"]No[/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]6. marts 2017[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0,2[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]21[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]8. marts 2017[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0,2[/TD]
[TD="align: center"]No[/TD]

</tbody>

Ark1​