Nesting "IF" statements

  • Thread starter Thread starter nca77
  • Start date Start date
N

nca77

I have 3 arguments that I am trying incorporate into one formula.

Here is a quick review of the "non-formula" driven data I currentl
have:

Sheet1, Row 1, Columns D through BK has month end dates starting wit
6/30/2004 and ending with 5/31/2009.

Sheet1, Row 2, Column C is a starting value of 11,065 in C2.

Sheet2, Row 1, Columns B through G have year end dates starting wit
12/31/2004 and ending with 12/31/2009.

Sheet2, Row 2, Columns B through G have growth rates of 10%.

The formula needs to be in Sheet1, Row 2, Columns D through BK.

Ok, now for the formula.

I need the formula to first scan the month end dates on Sheet1 and i
the year is 2004, it should grab data from different tab. Lets jus
call it "DiffTab!A1"

Then I need the formula to apply the 10% growth rate only in Decembe
of the corresponding year. So for instance Sheet 2, C1 says 12/31/200
and Sheet 1, V1 also says 12/31/2005.. I need the formula to take th
value for 1/31/2005 and multiply it by the 10%.. and that should b
value for 12/31/2005.

Finally if the year isn't 2004 and its not December.. I need the othe
months to "smooth" there way up to the December number (which shoul
now have a 10% growth from the January of that year).. For instance i
January was 100.. the December number should now be 110.. and the mont
in between should gradually work there way up to the 110.. so it woul
be December minus January divided by 12.. plus previous months value.

I think this can be achieved.. I came close but did not have success
Hopefully someone here can spend some time and help me out.

Thank yo
 
Hi
not completely sure about your layout but give the following a try
(question what should happen for Dec. 2004)?
D2:
=IF(YEAR(D1)=2004,'Difftab1'!D2,IF(MONTH(D1)=12,HLOOKUP(DATE(YEAR(D1)-1
,12,31),$C$1:$BK$2,2,0)*1.1,(HLOOKUP(DATE(YEAR(D1),12,31),$C$1:$BK$2,2,
0)-HLOOKUP(DATE(YEAR(D1)-1,12,31),$C$1:$BK$2,2,0))/12+C2))
 
Frank,

To answer your question: If the year is 2004, the formula should refe
to the DiffTab, regardless if the month is December.

The formula you provided does not "interact" with Sheet 2 at all.
should have mentioned that the growth rates (10%) will change eac
month. So there could be a 10% growth rate for 2005, a 25% growth rat
for 2006, etc.

So i need the formula to scan for the year and apply to growth rat
attributed to that year from Sheet2.

Thanks, Nic
 
Hi
you may provide some more details:
- where is the data located in sheet2
- how is the data structured

You may use VLOOKUP then to get the data. So something like
=VLOOKUP(YEAR(D1),'sheet2'!$A$1:$B$20,2,0)
 
Hi
this won't help me :-)
Reason: you're posting via Excelforum, but I'm directly connected to
the newsgroup. Your news update is not transfered through to the NG. So
post this new infomration i a follow-up post
 
Back
Top