can't figure it
 

I want to set a default annual growth rate for each of 40 years and then let
a user specify a different growth rate for up to 4 particular years within
those 40, ie the user will change the default setting. I want the user input
area to be a simple 4 x 2 table where the first column is the year number,
the second column the amended growth rate. A list of growth rates for each of
the 40 years will reside in the calculation zone of the model.

I imagine the formula for growth rate for each of the 40 rows will check if
the year number matches a year number in the 4 x 2 table; if there is not a
match then the value is set to the default growth rate number, if there is a
match then the value is set to the corresponding value in the 4 x 2 table.
Sounds easy enough but I can't figure it. Any suggestions please?

Hi,

Lets assume that the user matrix is in the range of cells A1 to B5, and
that your default growth rate is 10%, paste this formula in the cell
next to first year in the range and copy it down to the whole range(Year
Range here is assumed to be in F column. Change your lookup range
accordingly)

=IF(ISERROR(VLOOKUP(F2,$A$1:$B$5,2,FALSE)),10%,VLO OKUP(F2,$A$1:$B$5,2,FALSE))

Regards

Govind.

gvm wrote:
I want to set a default annual growth rate for each of 40 years and then let
a user specify a different growth rate for up to 4 particular years within
those 40, ie the user will change the default setting. I want the user input
area to be a simple 4 x 2 table where the first column is the year number,
the second column the amended growth rate. A list of growth rates for each of
the 40 years will reside in the calculation zone of the model.

I imagine the formula for growth rate for each of the 40 rows will check if
the year number matches a year number in the 4 x 2 table; if there is not a
match then the value is set to the default growth rate number, if there is a
match then the value is set to the corresponding value in the 4 x 2 table.
Sounds easy enough but I can't figure it. Any suggestions please?

Excellent Govind, thanks very much

"Govind" wrote:

Hi,

Lets assume that the user matrix is in the range of cells A1 to B5, and
that your default growth rate is 10%, paste this formula in the cell
next to first year in the range and copy it down to the whole range(Year
Range here is assumed to be in F column. Change your lookup range
accordingly)

=IF(ISERROR(VLOOKUP(F2,$A$1:$B$5,2,FALSE)),10%,VLO OKUP(F2,$A$1:$B$5,2,FALSE))

Regards

Govind.

gvm wrote:
I want to set a default annual growth rate for each of 40 years and then let
a user specify a different growth rate for up to 4 particular years within
those 40, ie the user will change the default setting. I want the user input
area to be a simple 4 x 2 table where the first column is the year number,
the second column the amended growth rate. A list of growth rates for each of
the 40 years will reside in the calculation zone of the model.

I imagine the formula for growth rate for each of the 40 rows will check if
the year number matches a year number in the 4 x 2 table; if there is not a
match then the value is set to the default growth rate number, if there is a
match then the value is set to the corresponding value in the 4 x 2 table.
Sounds easy enough but I can't figure it. Any suggestions please?