Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

migrated from stackoverflow.com Nov 18 '12 at 17:01

This question came from our site for professional and enthusiast programmers.

3 Answers

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.

share|improve this answer
add comment

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

enter image description here

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)

share|improve this answer
    
Hmm. That actually might work. I've never used VLookup, but this seems like a good time to learn. I'll check it out and see what I come up with. Thanks! –  user1832164 Nov 17 '12 at 21:13
add comment

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.

SU507364 example

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).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.