Writing a VBA program

[subvanpatent]

I am trying to write a script to help me determine the Mean Time Between
Repair for equipment. I played with it all day yesterday but was not able to
figure it out. Right now I have to enter the string manually. The formula I
am using is "=AVERAGE(C7-C6,C6-C5,C5-C4,C4-C3,C3-C2)" where C7 would be the
latest date to C2 the earliest date. As you can see this would become
cumbersome with numerous dates.

Thanks,

 

[Sam Wilson]

Hi,

I might be wrong here, but given that the mean is just the sum of all the
numbers divided by how many there are, the average is just

=(C7-C2)/6

And if the number of rows you have will increase it's just:

=($C7-$C$2)/(Row($C7) - 1)

 

[Joel]

Function MyAverage(ParamArray Target() As Variant)

MyAverage = 0
For Each num In Target
MyAverage = MyAverage + num
Next num
MyAverage = MyAverage / (UBound(Target) + 1)
End Function

 

[Sam Wilson]

Isn't that the average of the dates, rather than the average time between
dates?

 

[Joel]

How about this instead

Function MyAverage(Target As Range)

MyAverage = 0
For i = 2 To Target.Count
MyAverage = MyAverage + (Target(i) - Target(i - 1))
Next i
MyAverage = MyAverage / Target.Count
End Function

 

[Sam Wilson]

Yeah, that'll do it. I still like my way though!

Sam

 

[subvanpatent]

I figured out this is not a macro. How do you run it in an Excel program?

Thanks,

 

[subvanpatent]

Sam,

This is the average of the difference between two close dates. I felt it
might be more accurate then the average you suggested. They are fairly
close. I like to see how close.

Thanks,

 

[Sam Wilson]

Jim,

If you include:

function MyAverage(..)
blahblahblah
End Function

in your code, you can then enter "=MyAverage(A1:A10)" as a function on your
worksheet, just as you could use "=SUM(A1:A10)" etc.

 

[Sam Wilson]

Me again,

Yor original formula was "=Average(A-B,B-C,C-D,D-E)" (can't be bothered
writing the cell addresses so I've used A,B,C,D,E)

My point was that that average would be worked out as follows:
[(A-B) + (B-C) + (C-D) +...]/4 which is the same as:

[A + (B-B) + (C-C) + ... - E]/4 which is the same as

(A-E)/4

so it works out identical, never mind similar!

Sam

 

[subvanpatent]

Thanks again. I would really like to know how to use the programs you and
Joel wrote for future reference. One of these days I need to get my company
to pay for classes in VBA but I am not going to hold my breath.

Thanks,
--
Jim

Me again,

Yor original formula was "=Average(A-B,B-C,C-D,D-E)" (can't be bothered
writing the cell addresses so I've used A,B,C,D,E)

My point was that that average would be worked out as follows:
[(A-B) + (B-C) + (C-D) +...]/4 which is the same as:

[A + (B-B) + (C-C) + ... - E]/4 which is the same as

(A-E)/4

so it works out identical, never mind similar!

Sam

Sam,

This is the average of the difference between two close dates. I felt it
might be more accurate then the average you suggested. They are fairly
close. I like to see how close.

Thanks,

 

[Sam Wilson]

I've explained how to use it, it's a couple of posts ago! Here it is again.

If you include:

function MyAverage(..)
blahblahblah
End Function

in your code, you can then enter "=MyAverage(A1:A10)" as a function on your
worksheet, just as you could use "=SUM(A1:A10)" etc.

Thanks again. I would really like to know how to use the programs you and
Joel wrote for future reference. One of these days I need to get my company
to pay for classes in VBA but I am not going to hold my breath.

Thanks,
--
Jim

Me again,

Yor original formula was "=Average(A-B,B-C,C-D,D-E)" (can't be bothered
writing the cell addresses so I've used A,B,C,D,E)

My point was that that average would be worked out as follows:
[(A-B) + (B-C) + (C-D) +...]/4 which is the same as:

[A + (B-B) + (C-C) + ... - E]/4 which is the same as

(A-E)/4

so it works out identical, never mind similar!

Sam

Sam,

This is the average of the difference between two close dates. I felt it
might be more accurate then the average you suggested. They are fairly
close. I like to see how close.

Thanks,
--
Jim


:

Isn't that the average of the dates, rather than the average time between
dates?

:


Function MyAverage(ParamArray Target() As Variant)

MyAverage = 0
For Each num In Target
MyAverage = MyAverage + num
Next num
MyAverage = MyAverage / (UBound(Target) + 1)
End Function


:

I am trying to write a script to help me determine the Mean Time Between
Repair for equipment. I played with it all day yesterday but was not able to
figure it out. Right now I have to enter the string manually. The formula I
am using is "=AVERAGE(C7-C6,C6-C5,C5-C4,C4-C3,C3-C2)" where C7 would be the
latest date to C2 the earliest date. As you can see this would become
cumbersome with numerous dates.

Thanks,