Excel: link value once, then prevent change

Question

For some budgeting spreadsheets I'm working on, I'd like to link each month to a value (in this case, a percentage). However, if the original percentage is changed I ONLY want to change values going forward.

For example, let's say item one is budgeted at 10%, so each month reflects 10% of the total (which changes every month). If I decide to change that to 12% going forward, I don't want the previously linked values to also change from 10 to 12% (and throw off lots of other numbers).

My thought was to have a check box where if I placed an x, the values would be locked to the value at the time of placing the x and no longer change.

Is this possible? I know there are options for doing a paste special, but I'm creating this spreadsheet for someone who is not very Excel savvy, so I want it to be as seamless as possible.

Many thanks.

Answer 1

You could just use an IF statements and check on date.

Example: I want to use 10% for dates Jan and Feb and then 12% thereafter

    Date      %
1   Jan-12    10  =IF(A1<$A$3, 0.1, 0.12)
2   Feb-12    10  =IF(A2<$A$3, 0.1, 0.12)  
3   Mar-12    12  =IF(A3<$A$3, 0.1, 0.12) 

I am not sure this gives you exactly what you need but a variation on this theme should get you moving ahead.

Answer 2

You could use a lookup table with a rate for each month where there's a change:

Here, the lookup table is to the left and the values looked up to the right. You'll notice that I didn't enter a rate for April and the April entries are still picking up the rate for March. This is similar to a table a teacher uses to look up grades by range. It relies on VLOOKUP's TRUE argument, which says find the nearest match that's smaller in an ordered list.

The formula for the VLookup, in E2 in this example is:

=VLOOKUP(D2,$A$2:$B$4,2,TRUE)

Answer 3

For “someone who is not very Excel savvy” I’d recommend keeping things as simple as possible and suggest one way to achieve this is to ‘declare’ the percentage and calculate the results merely as % times value.

Since dealing in months a ‘regular’ 10% seems a little odd because at 10% per month a year’s worth is 120% rather than the 100% I would have expected. However, using that as a starting point, the results for a value of 100 would be as ‘Prediction’ in example. Here B3 is hard coded as 10% but C3 is =B3 and D3:M3 just copied across from C3.

But the sequence can be broken where required (in the example, in May) by overwriting the formula in F8 (ie =E8) with 12%. The formula in G8 remains as =F8 so 12% will apply for June onwards also (unless later hard coded again).