1

I have the following data sheet:

enter image description here

As you can see I have a date and a no which is a string.

Now I want to filter out all dates which have the same number in a +-20 day intervall around the date. So for example:

If the 12.2.2014 has the value a then it gets a 1 and if the 3.2.2014 has the value a it gets also a 1. In contrast if the value 15.1.2014 has the value a it gets a 0 because it is not in the +-20 day range. If there exists two rows like in the example below with 10.07.2002 and value d then it gets a 0 because there is no other day in a 20 day intervall around it.

enter image description here

My implementation idea is: Calculate for each value and date the difference of the dates and if it is less or equal than 20 and greater and equal to 1 then give out a 1 else a 0.

Can this be done in excel?

I really appreciate any suggestions

PS.: I hope that I showed the problem clearly!

UPDATE

Using this formula I get:

=COUNTIFS($B$2:$B$32;B2;$A$2:$A$32;">"&(A2-20);$A$2:$A$32;"<"&(A2+20))-1

enter image description here

see col E.

user2051347
  • 1,609
  • 4
  • 23
  • 34

1 Answers1

2

Something like this (adjust the $B$2:$B$32 etc to your actual data range):

 =COUNTIFS($B$2:$B$32,B2,
                $A$2:$A$32,">" & (A2-20),
                $A$2:$A$32,"<" & (A2+20))-1

-1 is to avoid counting the row the formula is on.

To give only 0 or 1:

 =MIN(COUNTIFS($B$2:$B$32,B2,
                $A$2:$A$32,">" & (A2-20),
                $A$2:$A$32,"<" & (A2+20))-1,1)
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Wow thx for your answer! Please see my update if I adapted the formula right. Is it also possible to only give `0` and `1` values out? – user2051347 Feb 12 '14 at 18:52