Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
minimum a fixed figure when it is lower | New Users to Excel | |||
Excel will not allow me to change a numerical figure in a specifi. | Excel Discussion (Misc queries) | |||
How can I make a timesheet to figure my hours and payrate? | Excel Worksheet Functions | |||
how to type a minues figure | New Users to Excel | |||
excel to figure miles per gallon | New Users to Excel |