nested if functions?

  • Thread starter Struggling of Essex
  • Start date
S

Struggling of Essex

I am producing a cashflow. It is over five years, I have established that
average contract values for 2006, 2007, 2008 2009 and 2010 will be 800k,
1200k, 2000k, 2500k and 3000k respectively. I want the cell with the contract
value to change automatically if a project start date is entered, eg if a
project starts in March 2007 its value will be 1200k.

Can anyone help me?
 
B

Bernard Liengme

Here are some ideas:
a) =CHOOSE(A10-2000,1200,1500,1600........)
b)=IF(A11=2006,2000,IF(A11=2007,2000,IF(A11=2008,2500,0)))
come back with specific question
best wishes
 
P

pinmaster

Try this:
=LOOKUP(YEAR(A1),{2006,2007,2008,2009,2010},{800,1200,2000,2500,3000})

if your looking to add the "K" at the end of the value then add this t
the end of the formula

&"K"

HTH
J
 
S

Struggling of Essex

Many thanks but to be more specific

The cells within column D2:D60 have specific commencment dates for a project
e.g. project 1 commences 13 March 2007, therefore D2 has this date, I want E2
to show the value 800,000 automatically. Similarly D3' commencment date is 11
July 2008, therefore I would like the adjacent e3 to show 1,200,000. I am
showing values for 2007, 2008, 2009, 2010 and 2011.

Project values for projects commencing in 2007 will be 800,000
Project values for projects commencing in 2008 will be 1,200,000
Project values for projects commencing in 2009 will be 2,000,000
Project values for projects commencing in 2010 will be 2,500,000
Project values for projects commencing in 2011 will be 3,000,000

I hope you can help me

Thanks.
 
S

Struggling of Essex

Many thanks but to be more specific

The cells within column D2:D60 have specific commencment dates for a project
e.g. project 1 commences 13 March 2007, therefore D2 has this date, I want E2
to show the value 800,000 automatically. Similarly D3' commencment date is 11
July 2008, therefore I would like the adjacent e3 to show 1,200,000. I am
showing values for 2007, 2008, 2009, 2010 and 2011.

Project values for projects commencing in 2007 will be 800,000
Project values for projects commencing in 2008 will be 1,200,000
Project values for projects commencing in 2009 will be 2,000,000
Project values for projects commencing in 2010 will be 2,500,000
Project values for projects commencing in 2011 will be 3,000,000

I hope you can help me
 
P

pinmaster

Try this then:

=IF(D2="","",LOOKUP(YEAR(D2),{2006,2007,2008,2009,2010},{800000,1200000,2000000,2500000,3000000}))

copied down

Regards
JG
 
R

Ron Rosenfeld

I am producing a cashflow. It is over five years, I have established that
average contract values for 2006, 2007, 2008 2009 and 2010 will be 800k,
1200k, 2000k, 2500k and 3000k respectively. I want the cell with the contract
value to change automatically if a project start date is entered, eg if a
project starts in March 2007 its value will be 1200k.

Can anyone help me?
Click to expand...

Set up a table some place:

2006 $800k
2007 $1200k
2008 $2000k
2009 $2500k
2010 $3000k


Use this formula:

=VLOOKUP(YEAR(Start_Date),Table,2)

Start_date refers to a cell containing your start date as a normal excel date.

Table refers to the range where the above table is located.
--ron
 
Top