Here is example #1:
b1 = $300 total project cost
c1 = 3 number of years for this project
d1 = $100 intend to spend in 2005, formula: b1/c1
e1 = $100 intend to spend in 2006, formula: b1/c1
f1 = $100 intend to spend in 2007, formula: b1/c1
g1 = empty cell
What I need to happen when I enter 0 (zero) into d1:
b1 = $300 total project cost
c1 = 3 number of years for this project
d1 = $0 intend to spend in 2005
e1 = $100 intend to spend in 2006, formula: b1/c1 moved from former d1 cell
f1 = $100 intend to spend in 2007, formula: b1/c1 moved from former e1 cell
g1 = $100 intend to spend in 2008, formula: b1/c1 moved from former f1 cell
h1 = empty cell
Here is example #2:
b1 = $300 total project cost
c1 = 3 number of years for this project
d1 = $100 intend to spend in 2005, formula: b1/c1
e1 = $100 intend to spend in 2006, formula: b1/c1
f1 = $100 intend to spend in 2007, formula: b1/c1
g1 = empty cell
what I need to happen when I enter 40 into d1:
b1 = $300 total project cost
c1 = 3 number of years for this project
d1 = $40 intend to spend in 2005
the remaining balance of ($300 - $40 spent in 2005 should be divided by the
remaining 2 years left on this project)
e1 = $130 intend to spend in 2006, formula: (b1-40)/(c1-1)
f1 = $130 intend to spend in 2007, formula: (b1-40)/(c1-1)
g1 = empty cell
Here is example #3:
b1 = $100 total project cost
c1 = 4 number of years for this project
d1 = $25 intend to spend in 2005 (25% of budget), formula: b1*0.25
e1 = $30 intend to spend in 2006 (30% of budget), formula: b1*0.30
f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30
g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15
h1 = empty cell
What I need to happen when I enter 0 (zero) into d1:
b1 = $100 total project cost
c1 = 4 number of years for this project
d1 = $0 intend to spend in 2005
e1 = $30 intend to spend in 2006 (25% of budget), formula: b1*0.25
f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30
g1 = $30 intend to spend in 2008 (30% of budget), formula: b1*0.30
h1 = $15 intend to spend in 2009 (15% of budget), formula: b1*0.15
i1 = empty cell
Here is example #4:
b1 = $100 total project cost
c1 = 4 number of years for this project
d1 = $25 intend to spend in 2005 (25% of budget), formula: b1*0.25
e1 = $30 intend to spend in 2006 (30% of budget), formula: b1*0.30
f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30
g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15
h1 = empty cell
What I need to happen when I enter 11 into d1:
b1 = $100 total project cost
c1 = 4 number of years for this project
d1 = $11 intend to spend in 2005
the remaining balance of ($100 - $11 spent in 2005 should be divided by the
remaining 3 years left on this project as follows: 15% should be paid in the
last year of the project – 2008 and the remaining amount should be divided
equally among other years, 2006 & 2007 in this example)
e1 = $37 intend to spend in 2006, formula: (b1-d1-g1)/(c1-2)
f1 = $37 intend to spend in 2007, formula: (b1-d1-g1)/(c1-2)
g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15
h1 = empty cell
I have some other combinations. But these 2 are the most common.
Thank you.