Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Step thru Dates
I want to perform a calculation on a range of dates as follows
Date 1 - 2-7-07 Date 2 - 8-7-07 Date 3 - 2-7-08 Date 4 - 8-7-08 For output 1 I would like to multiply 5*(date2-date1) For output 2 I would like to multiply 5*(date3-date2) For output 3 I would like to multiply 5*(date4-date3) and then store those values... multiply each of the outputs as FINAL1. Then step down one step and have: For output 2b I would like to multiply 5*(date3-date2) For output 3b I would like to multiply 5*(date4-date3) and then store those values... multiply each of the outputs as FINAL2. simple if my number of dates doesn't change, but what if I have ten dates one time and 3 the next? How can I get a variable number of outputs/Finals? THANKS SO MUCH! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Step thru Dates
Assume the data is in A1
Dim output() as double Dim field1 as Double Dim lastrow as long, i as long lastrow = cells(rows.count,1).end(xlup).row redim output(1 to lastrow - 1) for i = 2 to lastrow output(i-1) = (cells(i,1)-cells(i-1,1))*5 Next field1 = 1 for i = 1 to lastrow - 1 field1 = output(i) * field1 Next you have the information in output to get the other information you want. -- Regards, Tom Ogilvy "John" wrote: I want to perform a calculation on a range of dates as follows Date 1 - 2-7-07 Date 2 - 8-7-07 Date 3 - 2-7-08 Date 4 - 8-7-08 For output 1 I would like to multiply 5*(date2-date1) For output 2 I would like to multiply 5*(date3-date2) For output 3 I would like to multiply 5*(date4-date3) and then store those values... multiply each of the outputs as FINAL1. Then step down one step and have: For output 2b I would like to multiply 5*(date3-date2) For output 3b I would like to multiply 5*(date4-date3) and then store those values... multiply each of the outputs as FINAL2. simple if my number of dates doesn't change, but what if I have ten dates one time and 3 the next? How can I get a variable number of outputs/Finals? THANKS SO MUCH! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Step thru Dates
Tom, so sorry I want to put this in a User Defined Function, which I believe
you cannot select cells in..... Dates would be a range the user inputs for the function. "Tom Ogilvy" wrote: Assume the data is in A1 Dim output() as double Dim field1 as Double Dim lastrow as long, i as long lastrow = cells(rows.count,1).end(xlup).row redim output(1 to lastrow - 1) for i = 2 to lastrow output(i-1) = (cells(i,1)-cells(i-1,1))*5 Next field1 = 1 for i = 1 to lastrow - 1 field1 = output(i) * field1 Next you have the information in output to get the other information you want. -- Regards, Tom Ogilvy "John" wrote: I want to perform a calculation on a range of dates as follows Date 1 - 2-7-07 Date 2 - 8-7-07 Date 3 - 2-7-08 Date 4 - 8-7-08 For output 1 I would like to multiply 5*(date2-date1) For output 2 I would like to multiply 5*(date3-date2) For output 3 I would like to multiply 5*(date4-date3) and then store those values... multiply each of the outputs as FINAL1. Then step down one step and have: For output 2b I would like to multiply 5*(date3-date2) For output 3b I would like to multiply 5*(date4-date3) and then store those values... multiply each of the outputs as FINAL2. simple if my number of dates doesn't change, but what if I have ten dates one time and 3 the next? How can I get a variable number of outputs/Finals? THANKS SO MUCH! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Step thru Dates
That is true. You can't. but of what possible relevance is it to my answer.
I didn't select any cells???? Of course, for a UDF, I would pass the range to be evaluated in through the argument list Public Function Foo(rng As Range) Dim output() As Double Dim field1 As Double Dim i As Long If rng.Columns.Count 1 Or rng.Areas.Count 1 Then Foo = CVErr(xlErrRef) Exit Function End If lastrow = rng.Count ReDim output(1 To lastrow - 1) For i = 2 To lastrow output(i - 1) = (rng(i) - rng(i - 1)) * 5 Next field1 = 1 For i = 1 To lastrow - 1 field1 = output(i) * field1 Next Foo = field1 End Function usage =Foo(B3:B10) the above gives an answer although it is illustrative since your ask for multiple results and type of results without a clear explanation of what to do with them. -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "John" wrote: Tom, so sorry I want to put this in a User Defined Function, which I believe you cannot select cells in..... Dates would be a range the user inputs for the function. "Tom Ogilvy" wrote: Assume the data is in A1 Dim output() as double Dim field1 as Double Dim lastrow as long, i as long lastrow = cells(rows.count,1).end(xlup).row redim output(1 to lastrow - 1) for i = 2 to lastrow output(i-1) = (cells(i,1)-cells(i-1,1))*5 Next field1 = 1 for i = 1 to lastrow - 1 field1 = output(i) * field1 Next you have the information in output to get the other information you want. -- Regards, Tom Ogilvy "John" wrote: I want to perform a calculation on a range of dates as follows Date 1 - 2-7-07 Date 2 - 8-7-07 Date 3 - 2-7-08 Date 4 - 8-7-08 For output 1 I would like to multiply 5*(date2-date1) For output 2 I would like to multiply 5*(date3-date2) For output 3 I would like to multiply 5*(date4-date3) and then store those values... multiply each of the outputs as FINAL1. Then step down one step and have: For output 2b I would like to multiply 5*(date3-date2) For output 3b I would like to multiply 5*(date4-date3) and then store those values... multiply each of the outputs as FINAL2. simple if my number of dates doesn't change, but what if I have ten dates one time and 3 the next? How can I get a variable number of outputs/Finals? THANKS SO MUCH! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Step thru Dates
:) thanks for the help, you my foo too!
"Tom Ogilvy" wrote: That is true. You can't. but of what possible relevance is it to my answer. I didn't select any cells???? Of course, for a UDF, I would pass the range to be evaluated in through the argument list Public Function Foo(rng As Range) Dim output() As Double Dim field1 As Double Dim i As Long If rng.Columns.Count 1 Or rng.Areas.Count 1 Then Foo = CVErr(xlErrRef) Exit Function End If lastrow = rng.Count ReDim output(1 To lastrow - 1) For i = 2 To lastrow output(i - 1) = (rng(i) - rng(i - 1)) * 5 Next field1 = 1 For i = 1 To lastrow - 1 field1 = output(i) * field1 Next Foo = field1 End Function usage =Foo(B3:B10) the above gives an answer although it is illustrative since your ask for multiple results and type of results without a clear explanation of what to do with them. -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "John" wrote: Tom, so sorry I want to put this in a User Defined Function, which I believe you cannot select cells in..... Dates would be a range the user inputs for the function. "Tom Ogilvy" wrote: Assume the data is in A1 Dim output() as double Dim field1 as Double Dim lastrow as long, i as long lastrow = cells(rows.count,1).end(xlup).row redim output(1 to lastrow - 1) for i = 2 to lastrow output(i-1) = (cells(i,1)-cells(i-1,1))*5 Next field1 = 1 for i = 1 to lastrow - 1 field1 = output(i) * field1 Next you have the information in output to get the other information you want. -- Regards, Tom Ogilvy "John" wrote: I want to perform a calculation on a range of dates as follows Date 1 - 2-7-07 Date 2 - 8-7-07 Date 3 - 2-7-08 Date 4 - 8-7-08 For output 1 I would like to multiply 5*(date2-date1) For output 2 I would like to multiply 5*(date3-date2) For output 3 I would like to multiply 5*(date4-date3) and then store those values... multiply each of the outputs as FINAL1. Then step down one step and have: For output 2b I would like to multiply 5*(date3-date2) For output 3b I would like to multiply 5*(date4-date3) and then store those values... multiply each of the outputs as FINAL2. simple if my number of dates doesn't change, but what if I have ten dates one time and 3 the next? How can I get a variable number of outputs/Finals? THANKS SO MUCH! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Drop Down List with Step by Step Instructions for 2007 | Excel Worksheet Functions | |||
Need step by step to add invoice numbering to excel template | New Users to Excel | |||
Step by step tell me cube root (2197) which I know is 13 | Excel Discussion (Misc queries) | |||
What is the step-by-step procedure for making a data list? | Excel Discussion (Misc queries) | |||
I need step by step instructions to create a macro for 10 imbedde. | Excel Worksheet Functions |