Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing a VBA program
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, -- Jim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing a VBA program
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) "subvanpatent" wrote: 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, -- Jim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing a VBA program
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 "subvanpatent" wrote: 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, -- Jim |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing a VBA program
Isn't that the average of the dates, rather than the average time between
dates? "Joel" wrote: 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 "subvanpatent" wrote: 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, -- Jim |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing a VBA program
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" wrote: Isn't that the average of the dates, rather than the average time between dates? "Joel" wrote: 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 "subvanpatent" wrote: 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, -- Jim |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing a VBA program
Yeah, that'll do it. I still like my way though!
Sam "Joel" wrote: 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" wrote: Isn't that the average of the dates, rather than the average time between dates? "Joel" wrote: 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 "subvanpatent" wrote: 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, -- Jim |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing a VBA program
I figured out this is not a macro. How do you run it in an Excel program?
Thanks, -- Jim "Joel" wrote: 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" wrote: Isn't that the average of the dates, rather than the average time between dates? "Joel" wrote: 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 "subvanpatent" wrote: 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, -- Jim |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing a VBA program
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 "Sam Wilson" wrote: Isn't that the average of the dates, rather than the average time between dates? "Joel" wrote: 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 "subvanpatent" wrote: 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, -- Jim |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing a VBA program
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. "subvanpatent" wrote: I figured out this is not a macro. How do you run it in an Excel program? Thanks, -- Jim "Joel" wrote: 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" wrote: Isn't that the average of the dates, rather than the average time between dates? "Joel" wrote: 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 "subvanpatent" wrote: 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, -- Jim |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing a VBA program
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" wrote: 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 "Sam Wilson" wrote: Isn't that the average of the dates, rather than the average time between dates? "Joel" wrote: 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 "subvanpatent" wrote: 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, -- Jim |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing a VBA program
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 "Sam Wilson" wrote: 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" wrote: 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 "Sam Wilson" wrote: Isn't that the average of the dates, rather than the average time between dates? "Joel" wrote: 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 "subvanpatent" wrote: 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, -- Jim |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing a VBA program
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. "subvanpatent" wrote: 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 "Sam Wilson" wrote: 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" wrote: 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 "Sam Wilson" wrote: Isn't that the average of the dates, rather than the average time between dates? "Joel" wrote: 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 "subvanpatent" wrote: 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, -- Jim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a way to unload the loaded XLL file in Excel? Hi all, I amdebugging XLL link library using Visual C++. Everytime I rebuild the XLL, Ihave to close the whole Excel program and relaunch the Excel program again,and then load in the newly gene | Excel Discussion (Misc queries) | |||
writing a text file in the same folder as my program | Excel Programming | |||
Writing a program to archive information | Excel Programming | |||
Re writing program - 3/4 rogrammers input on this spreadsheet, ! | Excel Programming | |||
merging excel program with tdc finance program | Excel Programming |