Different kind of cell reference.

[Howard Brazee]

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

 

[Howard Brazee]

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

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

 

[Lars-Åke Aspelin]

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

If the first formula in in cell D2, try this:

=D1+(D15-D1)*(ROW(D2)-ROW(D$1))

Hope this helps / Lars-Åke

 

[Howard Brazee]

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

 

[Lars-Åke Aspelin]

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

 

[Howard Brazee]

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

 

[Lars-Åke Aspelin]

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

 

[GS]

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!

 

[GS]

Howard Brazee explained on 6/4/2010 :

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,