From: Howard Brazee on 3 Jun 2010 15:48 On Thu, 03 Jun 2010 21:31:40 +0200, Lars-�ke Aspelin wrote: >>If the first formula in in cell D2, try this: >> >>=D1+(D15-D1)*(ROW(D2)-ROW(D\$1)) >> >>Hope this helps / Lars-�ke > >Oops, forgot to write that the formula in D2 can now be copied down >column D until cell D12. The trouble is, the (D\$1) will need to be manually changed to (D\$15) and so on each time I duplicate the set. I guess my manual code before is a bit easier to paste for each new year. No big deal, but I'd sort of like to have it more *coded*, if you know what I mean. (even if it's more work - it's more fun code!) -- "In no part of the constitution is more wisdom to be found, than in the clause which confides the question of war or peace to the legislature, and not to the executive department." - James Madison From: Lars-�ke Aspelin on 3 Jun 2010 16:23 On Thu, 03 Jun 2010 13:48:32 -0600, Howard Brazee wrote: >On Thu, 03 Jun 2010 21:31:40 +0200, Lars-�ke Aspelin > wrote: > >>>If the first formula in in cell D2, try this: >>> >>>=D1+(D15-D1)*(ROW(D2)-ROW(D\$1)) >>> >>>Hope this helps / Lars-�ke >> >>Oops, forgot to write that the formula in D2 can now be copied down >>column D until cell D12. > >The trouble is, the (D\$1) will need to be manually changed to (D\$15) >and so on each time I duplicate the set. > >I guess my manual code before is a bit easier to paste for each new >year. No big deal, but I'd sort of like to have it more *coded*, if >you know what I mean. (even if it's more work - it's more fun code!) Well, the formula I proposed need some corrections. =D\$1+(D\$15-D\$1)*(ROW(D2)-ROW(D\$1)) should work for the first year. In order to handle subsequent year without manual editing of formula we have to know where the subssequent year is located. Lars-�ke From: Howard Brazee on 3 Jun 2010 18:29 On Thu, 03 Jun 2010 22:23:09 +0200, Lars-�ke Aspelin wrote: >Well, the formula I proposed need some corrections. > >=D\$1+(D\$15-D\$1)*(ROW(D2)-ROW(D\$1)) > >should work for the first year. > >In order to handle subsequent year without manual editing of formula >we have to know where the subssequent year is located. I actually simplified my formula for the post. Pick a line. My example had it in line 15. -- "In no part of the constitution is more wisdom to be found, than in the clause which confides the question of war or peace to the legislature, and not to the executive department." - James Madison From: GS on 3 Jun 2010 23:13 Howard Brazee used his keyboard to write :> > I have a need to calculate a formula that I don't know how to > reference. The following is the pattern that I could hard code. > Basically, I have a value for each year, and need to interpolate > monthly values. Is there a more generic way of entering these > cells? > > =D1 + (D15-D1)*1 > =D1 + (D15-D1)*2 > =D1 + (D15-D1)*3 > =D1 + (D15-D1)*4 > =D1 + (D15-D1)*5 > =D1 + (D15-D1)*6 > =D1 + (D15-D1)*7 > =D1 + (D15-D1)*8 > =D1 + (D15-D1)*9 > =D1 + (D15-D1)*10 > =D1 + (D15-D1)*11 > =D1 + (D15-D1)*12 > > > =D30 + (D30-D15)*1 > =D30 + (D30-D15)*2 > =D30 + (D30-D15)*3 > =D30 + (D30-D15)*4 > =D30 + (D30-D15)*5 > =D30 + (D30-D15)*6 > =D30 + (D30-D15)*7 > =D30 + (D30-D15)*8 > =D30 + (D30-D15)*9 > =D30 + (D30-D15)*10 > =D30 + (D30-D15)*11 > =D30 + (D30-D15)*12 Where are you storing the value you have for each year? Are you trying to generate numbers for each month (1 to 12), or month names (Jan to Dec)? A sample of where your year values are stored and the expected results of the formula would be very helpful! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc From: Howard Brazee on 4 Jun 2010 08:22 On Thu, 03 Jun 2010 23:13:58 -0400, GS wrote: >Where are you storing the value you have for each year? Are you trying >to generate numbers for each month (1 to 12), or month names (Jan to >Dec)? > >A sample of where your year values are stored and the expected results >of the formula would be very helpful! I'm off today and the sheet is at work. I do some manipulation of date functions to display the date. I would have to do some other manipulation to get it back. I simplified what I'm doing for this thread. Make any assumptions about date that would illustrate your solution. -- "In no part of the constitution is more wisdom to be found, than in the clause which confides the question of war or peace to the legislature, and not to the executive department." - James Madison First  |  Prev  |  Next  |  Last