Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting a cell depending on the other cell's value, kind of | Links and Linking in Excel | |||
absolute cell reference A spreadsheet cell reference that does no | Excel Discussion (Misc queries) | |||
in vba what command is used to determine if a particular cell on a particular sheet changed? some kind of event? how to get the old and new value of the cell? | Excel Worksheet Functions | |||
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions |