Different kind of cell reference.
in [Programming]
|
From: Howard Brazee on 3 Jun 2010 15:11 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 -- "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: Howard Brazee on 3 Jun 2010 15:17 Oops, those multiplies should be from 0 through 11, not 1 through 12. On Thu, 03 Jun 2010 13:11:35 -0600, Howard Brazee <howard(a)brazee.net> wrote: > > >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 -- "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 15:27 On Thu, 03 Jun 2010 13:17:59 -0600, Howard Brazee <howard(a)brazee.net> wrote: >Oops, those multiplies should be from 0 through 11, not 1 through 12. > >On Thu, 03 Jun 2010 13:11:35 -0600, Howard Brazee <howard(a)brazee.net> >wrote: > >> >> >>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 If the first formula in in cell D2, try this: =D1+(D15-D1)*(ROW(D2)-ROW(D$1)) Hope this helps / Lars-�ke
From: Howard Brazee on 3 Jun 2010 15:30 On Thu, 03 Jun 2010 21:27:04 +0200, Lars-�ke Aspelin <larske(a)REMOOVEtelia.com> wrote: >If the first formula in in cell D2, try this: > >=D1+(D15-D1)*(ROW(D2)-ROW(D$1)) That looks promising. -- "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 15:31 On Thu, 03 Jun 2010 21:27:04 +0200, Lars-�ke Aspelin <larske(a)REMOOVEtelia.com> wrote: >On Thu, 03 Jun 2010 13:17:59 -0600, Howard Brazee <howard(a)brazee.net> >wrote: > >>Oops, those multiplies should be from 0 through 11, not 1 through 12. >> >>On Thu, 03 Jun 2010 13:11:35 -0600, Howard Brazee <howard(a)brazee.net> >>wrote: >> >>> >>> >>>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 > > >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. Lars-�ke
|
Next
|
Last
Pages: 1 2 3 Prev: Accessing the call stack in an Excel VBA macro? Next: Separate code from workbook?! |