Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How best to integrate/implement this?
I keep track of several vehicles and the lifespan of the replacement
parts for them. The operators turn in a useage log at the end of each day with the ending miles and operating hours, and our maintenance records log the miles and hours at which parts are replaced. Unfortunately, it is all too common to have the hours meter go out. And the replacement doesn't necessarily begin at zero! This made calculating the total hours lifespan of any part a convoluted mess of several calculations thorugh all the hours changes. I built a spreadsheet that contains a table of the parameter changes; now I can enter the actual vehicle parameters at time of maintenance and when the part was originally installed and it returns the actual hours lifespan. I have to use one worksheet per vehicle, because I'm trying to get by without macros - code confuses the others who might use this! (I'm wavering on this - it would be much easier to have a single user form than jump from sheet to sheet!) This is good for a simple report on one major item that needed replacement. But here's my dilemma: I need to integrate/implement this into a comprehensive Excel-based report. The comprehensive report worksheet lists all major items for all the vehicles. When I generate an update to the report, I enter the new end-of-day parameters for each vehicle and formulas subtract the item installation parameters from the ending parameters to return the item's current lifespan. A macro then inserts these current values into a Word report. Due to the convolutions in the engine hours, though, some of the returned values are three or four times too much, and some are even negative! Somehow I need to use my calculator to at generate a "fudge factor", or at best be inserted directly into the formulas to get a correct return value. How would those of you with experience integrate/implement somehting like this? Ed |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How best to integrate/implement this?
Sounds more like a management question than a VBA question. However, when I
was in management, fudge factors had to be based on something that could be measured, had been measured or should be measured. That means making up a form and having some of the operators log in and out when their hour meters fail. Take one or two vehicles whose drivers are willing to cooperate. Use that data to figure your fudge factor and save the forms to back up your actions. "Ed from AZ" wrote: I keep track of several vehicles and the lifespan of the replacement parts for them. The operators turn in a useage log at the end of each day with the ending miles and operating hours, and our maintenance records log the miles and hours at which parts are replaced. Unfortunately, it is all too common to have the hours meter go out. And the replacement doesn't necessarily begin at zero! This made calculating the total hours lifespan of any part a convoluted mess of several calculations thorugh all the hours changes. I built a spreadsheet that contains a table of the parameter changes; now I can enter the actual vehicle parameters at time of maintenance and when the part was originally installed and it returns the actual hours lifespan. I have to use one worksheet per vehicle, because I'm trying to get by without macros - code confuses the others who might use this! (I'm wavering on this - it would be much easier to have a single user form than jump from sheet to sheet!) This is good for a simple report on one major item that needed replacement. But here's my dilemma: I need to integrate/implement this into a comprehensive Excel-based report. The comprehensive report worksheet lists all major items for all the vehicles. When I generate an update to the report, I enter the new end-of-day parameters for each vehicle and formulas subtract the item installation parameters from the ending parameters to return the item's current lifespan. A macro then inserts these current values into a Word report. Due to the convolutions in the engine hours, though, some of the returned values are three or four times too much, and some are even negative! Somehow I need to use my calculator to at generate a "fudge factor", or at best be inserted directly into the formulas to get a correct return value. How would those of you with experience integrate/implement somehting like this? Ed |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How best to integrate/implement this?
Thanks for the response. I can't go into details, but it's definitely
not a management issue - it's just "the way things are". And I have all the data for the changes to the parameters - that's what I'm using for my "calculator". My table looks like this: Meter Meter Meter Meter Instl-MI Instl-HR Rmvd-MI Rmvd-HR 5165.1 1036.8 5563.6 1062.9 5563.6 349.5 5696.8 357.2 5696.8 171.7 7042.5 267.6 Note that the Meter Rmvd miles on the first line matches the Meter Instl miles of the second line. I calculate out the total hours from each meter. And by making the Rmvd miles and hours in the last line the current vehicle parameters, I can get the total hours to date thought all the convolutions. My "calculator" lets me enter the miles and hours at item failure and the miles and hours at install, and returns the total hours lifespan. For instance, for an item installed at 5200 miles and 1040 hours (indicated), with the current parameters as shown in the last line of the table, I get a lifespan of 126.5 hours. As I said, I set this up to be on one worksheet per vehicle. and in a separate workbook from my report master. The question is: how can I use this when my VBA generates an updated report from the master to return the corrected total lifespan for each item? Perhaps link these worksheets into the report master and have the update code pick up each and every parameter set and run it through the correct calculator? Is there an easier way? Or just "brute strength"? Ed On Dec 10, 12:16*pm, JLGWhiz wrote: Sounds more like a management question than a VBA question. *However, when I was in management, fudge factors had to be based on something that could be measured, had been measured or should be measured. *That means making up a form and having some of the operators log in and out when their hour meters fail. *Take one or two vehicles whose drivers are willing to cooperate. *Use that data to figure your fudge factor and save the forms to back up your actions. "Ed from AZ" wrote: Due to the convolutions in the engine hours, though, some of the returned values are three or four times too much, and some are even negative! *Somehow I need to use my calculator to at generate a "fudge factor", or at best be inserted directly into the formulas to get a correct return value. How would those of you with experience integrate/implement somehting like this? Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
integrate flow | Excel Discussion (Misc queries) | |||
how do I integrate MapPoint into Excel? | Excel Discussion (Misc queries) | |||
how can I integrate in Excel? | Excel Discussion (Misc queries) | |||
How Do I Integrate? | Excel Discussion (Misc queries) | |||
integrate | Charts and Charting in Excel |