Step thru Dates

[John]

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!

 

[Tom Ogilvy]

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.

 

[John]

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]

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]

thanks for the help, you my foo too!

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.