TechArena Community Need help in excel formula
 User Name Remember Me? Password

#1
27-10-2008
 Member Join Date: Feb 2006 Posts: 214
Need help in excel formula

I would like to incorporate a function in a table but I am novice in this area and I really need your help. In my database, I have a column number entitled of contract and 3 other columns for different periods of this market (period1: 05 July 2006 to July 04, 2007, period 2: 05 July 2007 to 04 July 2008 etc). I hope that the period of the market is automated in another sheet. So I created a formula that will give me the market depending on the date. I guess it's like, if date is = or <dates of the first period of the contract; "1" etc as this comparison should be made on the 3 periods of the market. Thank you in advance for your help.
#2
27-10-2008
 Member Join Date: Dec 2007 Posts: 1,592
Re: Need help in excel formula

I believe there should be a testing so. For example take 3 periods:

P1: 01/01/06 - 31/12/06
P2: 01/01/07 - 31/12/07
P3: 01/01/08 - 31/12/08

then the formula would be to integrate (whereas the cell in which the reference date is A1): = IF (AND (A1> = 01/01/06, A1 <= 31/12/06), 1, IF (AND (A1> = 01/01/07, A1 <= 31/12/07) 2 ; IF (AND (A1> = 01/01/08, A1 <= 31/12/08), 3, 0)))

Normally it should work. But of course you must adopt the formula to your own case.
#3
27-10-2008
 Member Join Date: Feb 2006 Posts: 214
Re: Need help in excel formula

Thank you for your reply. The formula works because I've adopted to my case but it does not give me the expected result. Indeed, the market falls but does not correspond to reality. This is probably the formula that is not suited to me. Here are the elements in my example if you can help me or if someone else can do:

Cell G1 = period 1 market, dates of period are 1 in G2 and G3. Cell H1 = period 2 market , dates of period 2 in H2 and H3 Cell I1 = period 3 market, dates of period 3 in I2 and I3. J1 cell corresponds to the date.

In a cell for example K1, I wish to insert a formula that will give me the exact period of the contract contained in G1, H1 or I1 compared to the current date and dates of the period included in the G2/G3 cells, H2 / H3, I2/I3.

Thank you for any reply.

 Tags: