Excel 2007 : Restrict formula to a five-year window before an event date

**glux** · 02-07-2012, 07:15 AM

Hi guys!!

Thanks to Forum Gurus Marcol and zbor, I could solve the 2 first steps of my problem: to give a "similarity" score to each code from a list of 4-digit codes, limited to each acquirer.

And that's the awesome formula they came up with:

=MAX(IF(SUMPRODUCT(--($A$2:$A$1678&$B$2:$B$1678=A2&B2))>1,4,0),IF(SUMPRODUCT(--(LEFT($A2&$B2,10)=LEFT($A$2:$A$1678&$B$2:$B$1678,10)))-1>0,3,0),IF(SUMPRODUCT(--(LEFT($A2&$B2,9)=LEFT($A$2:$A$1678&$B$2:$B$1678,9)))-1>0,2,0),IF(SUMPRODUCT(--(LEFT($A2&$B2,8)=LEFT($A$2:$A$1678&$B$2:$B$1678,8)))-1>0,1,0))

Now the final problem I'm facing is that I have to restrict this formula to a 5-year window before an acquisition happened (identifiable by a starting date and an end date, or just the end date minus 5 years).

Here is an example:

"Acquiror's Code" "Target industry code" "Y-5 prior announce" "Date Announced" "Number of acquisitions in the past 5 years" "Target-to-target similarity among all targets" "Target-to-target similarity in 5-year window"
2003030 1311 6/18/2004 06/18/2009 0 4 0
2003030 1311 1/19/1996 01/19/2001 4 4 4
2003030 1311 11/16/1993 11/16/1998 4 4 4
2003030 4924 10/11/1991 10/11/1996 3 0 0
2003030 1311 10/1/1991 10/01/1996 2 4 4
2003030 1382 2/9/1991 02/09/1996 1 2 2
2003030 1311 1/4/1991 01/04/1996 0 4 0
2023748 4911 11/14/2003 11/14/2008 3 2 2
2023748 5172 11/14/2002 11/14/2007 2 1 0
2023748 1311 3/23/2000 03/23/2005 7 4 4
2023748 4924 3/18/2000 03/18/2005 6 2 2
2023748 1311 9/17/1996 09/17/2001 6 4 4
2023748 1311 7/10/1996 07/10/2001 5 4 4
2023748 1311 2/20/1996 02/20/2001 4 4 4
2023748 1382 8/1/1995 08/01/2000 3 2 2
2023748 1311 4/17/1995 04/17/2000 3 4 …
2023748 4931 3/31/1995 03/31/2000 2 2
2023748 5541 2/14/1995 02/14/2000 3 1
2023748 8731 7/6/1990 07/06/1995 2 0
2023748 1311 3/22/1990 03/22/1995 1 4
2023748 1311 3/21/1990 03/21/1995 0 4
2024763 1311 12/28/2001 12/28/2006 0 4
2024763 1311 7/8/1994 07/08/1999 1 4
2024763 1311 1/2/1993 01/02/1998 0 4

COLUMN C ("Y-5 prior announce") and COLUMN E (# of acquisitions in the past 5 years) are not necessary I believe as the even date is specified.

You can work on the excel file attach if needed: target-to-target similarity 7.xlsx

Thanks in advance,

Glux

**zbor** · 02-07-2012, 07:18 AM

Why is G10 = 0?

THis return all correct results except that one:

=IF(E2>0, formula, 0)

**glux** · 02-07-2012, 07:21 AM

Because if you look in the 5 years before G10 happened, there's not a single other acquisition starting with the same digit of the "Target industry code"

**zbor** · 02-07-2012, 07:22 AM

Also, usually is better to post just last questions (and if you need explanations step to step we can guide you).

Because sometimes end result my vary on input data and don't need to be like SUM of all 3.

Also, for us those a 3 different cases and we spend 3 more time to solve each of it :/

**glux** · 02-07-2012, 07:26 AM

I totally understand your point!! And that's exactly what I thought as well... that's why I posted my question with the overall problem 2 days ago... but no one answer my thread, even though it was seen 122 times since!

**zbor** · 02-07-2012, 07:39 AM

See is this OK for further values:

Edit: Keep ranges as low as you can (like here $A$2:$A$1678 etc) because it will be slow for big ranges.

**glux** · 02-07-2012, 10:03 AM

I AM SOOOO HAPPY RIGHT NOW!!! Thanks sooo much!! I'm done with data collection for my thesis... woohoo... I can move on to the statistical analysis. So exciting ;-)