Different kind of cell reference.
 

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

Different kind of cell reference.
 

Oops, those multiplies should be from 0 through 11, not 1 through 12.

On Thu, 03 Jun 2010 13:11:35 -0600, Howard Brazee
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

Different kind of cell reference.
 

On Thu, 03 Jun 2010 13:17:59 -0600, Howard Brazee
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
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

Different kind of cell reference.
 

On Thu, 03 Jun 2010 21:27:04 +0200, Lars-Åke Aspelin
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

Different kind of cell reference.
 

On Thu, 03 Jun 2010 21:27:04 +0200, Lars-Åke Aspelin
wrote:

On Thu, 03 Jun 2010 13:17:59 -0600, Howard Brazee
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
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

Different kind of cell reference.
 

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

Different kind of cell reference.
 

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

Different kind of cell reference.
 

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

Different kind of cell reference.
 

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

Different kind of cell reference.
 

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

Different kind of cell reference.
 

Howard Brazee explained on 6/4/2010 :
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.

Try this...

Copy this sub into a module:

Sub InterpolateMonthlyValues(YearsToDo As String)
Dim i As Long, r As Long
Dim s1 As String, s2 As String, f1 As String, f2 As String, f3 As
String
Dim vYrs As Variant

r = 1
f1 = "=month(text(row()-"
f2 = "&""/1/""&D$"
f3 = ",""mm/dd/yyyy""))"

vYrs = Split(YearsToDo, ",")
For i = LBound(vYrs) To UBound(vYrs)
s1 = f1 & CStr(r) & f2 & CStr(r) & f3
s2 = "D" & CStr(r)
With Range(s2)
.value = vYrs(i)
With .Offset(1).Resize(12)
.Formula = s1
' .value = .value 'convert to constants if desired
End With
End With
'Insert 1 blank row between groupings
r = r + 14 'spacing: adjust to suit
Next
End Sub

Enter this in the Immediate Window:
InterpolateMonthlyValues "2007,2008,2009,2010"

Hopefully, you'll be able to modify it to suit your need if it's not
exactly what you want 'as is'.

regards,

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Different kind of cell reference.
 

On Fri, 04 Jun 2010 13:21:50 -0400, GS wrote:

Hopefully, you'll be able to modify it to suit your need if it's not
exactly what you want 'as is'.

Interesting. That will be fun to play with when I have time. Thanks.

--
"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