• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Light Bulb Lifespan Model

A

Andrew M

New Member
Dear Chandoo


I have posted the same question in the blog post "We want you - revisited" and Narayan suggested I put it here also.


For some time I’ve been wanting to try a simulation of light bulb failures in Excel. The problem originated with a reliability study I did on an aircraft component which contains 70 tiny electric light bulbs, yes, the Edison type with a filament! These bulbs required replacing in a workshop thus the unit got a bad name for failing in service every few hours. The unit would be removed, light bulb replaced and refitted, only to fail again after a few hours service when the next most tired bulb failed. My gut-feel told me to replace all 70 bulbs once so that all start out with a new life rather than have a range of expected lives from ‘about to fail’ to new. The units typically had a total life of 20 to 30 thousand hours and over this lifetime each bulb would have been replaced many times.


This question has kept me puzzled for a long time and I cannot get my Excel knowledge around the simulation. My thoughts are to have a cell for total unit life (fixed for 1 simulation), a cell for expected bulb life (say 1000 hours), a cell for percentage variance of life (say +/- 10%) and a row representing each bulb. Each cell in the row needs to calculate a random life (expected life plus or minus variance) and add it to the previous achieved life. The total of each row must equal the unit total life. My output would be a horizontal stacked bar chart with each cell giving one chart data point.


Any thoughts or ideas would be appreciated.


Andrew
 
Good day Andrew M


I dare say you have done this but I will just ask so as to clarify. With the amount of work you have to do on these units to replace the filament bulbs, has any one looked at replacement bulbs of none filament type, may be more expensive in the outlay but would offset the cost by greatly cutting maintenance costs.
 
Good Day bobhc


Replacing the filament bulbs with something more durable such as LEDs was regrettably out of the question. The unit is a read-out display used on commercial aircraft of 1960s/1970s design. Any change in the design would have been subject to testing and re-certification to prove that the modified design works as well as the original. The original worked well as long as every bulb worked. Re-certification is a long and expensive process. In the late 1990s with the aircraft type already slated to go out of service, the company I worked for did not want to spend large amounts of money. We only had to keep them going for a few years, hence replacing the individual bulbs was the done thing.


Although it may negatively influence someone thinking of a solution, this question has become a purely academic one. The units are long out of use today (been replaced with digital display screens) but the question has still gnawed at my mind. Can this type of model be created in Excel and how.
 
Hi Andrew ,


Good that you have posted your question here ; since it is the weekend , please wait for a day or two , for answers to come in.


In the meantime , I'd like to confirm what you have explained :


1. You will have one cell for the Expected Cell Life ; I assume that this will apply to all the bulbs in the system.


2. You will have one cell for the Expected Unit Life ; I assume this is independent of the Expected Cell Life.


3. You will have one cell for the Variance Factor , which will be applied to each bulb to calculate its actual life.


4. Now , you wish to have say 70 cells to model the 70 bulbs in a unit.


My question is , what will be the starting values for the simulation ?


Wouldn't it be better if each iteration put the new values in the next row , so that over 100 iterations , we would be able to track the behaviour of any individual bulb ? This would allow us to see the overall failure percentage for different values of the Variance Factor , or Expected Cell Life.


Are you comfortable with macros ?


Narayan
 
Hi Narayan


Thank you for your interest in this question. Let me answer your questions in the order you have put them.


1. Cell for the expected bulb life: this is the nominal life I would expect from each bulb individually.


2. Cell for expected unit life: this is the total life that the unit has been used for. Yes, it is independent of the bulb life and adds up for every hour the unit is in service.


3. Cell for variance factor: this is a factor to introduce and control how much the actual bulb life varies from it's nominal life. If all bulbs failed after 1000 hours all would fail at the same time, after the first 1000 hours in service. In reality this does not happen.


4. My thought was that the 70 rows modeled a unit with each row representing one bulb.


The starting values would be with the unit life at say 20 000 hours. What I want to see is how the failure of each bulb independently at a different time contributes to a very staggered expected life left of each individual bulb.


The cells mentioned in answer 1, 2 and 3 are intended to be variables used in the calculations. If I wanted to look at a unit with 5000 hours life I would change that cell value, the same with expected individual bulb life and variance.


I'm comfortable with macros which have some explanatory comments. My knowledge is not up to reading and understanding pure code, still learning that!


Andrew


P.S. I see India has just won the cricket Champions Trophy! Congratulations.
 
Hi Andrew ,


Let me correct my earlier ideas based on what you have posted :


1. What I had mentioned as Expected Unit Life is really Actual Unit Life.


2. Each row in one column represents one bulb ; 70 rows represent one unit. Does this mean that you will work with several units at a time ?


The only question is what next ? How do we start ?


We need two triggers , one to add an hour to each bulb's life , and to the unit life , and the other to signal a bulb's failure.


Once this is done , what is to be done when a bulb fails ? This will have to be replaced for the unit to continue to function , which means whenever a bulb fails , a snapshot of the entire 70 rows for that unit will have to be stored.


Can you , and others too , comment ?


Narayan
 
Andrew


You are going to need a Failure Distribution for the Bulbs

This may be collected from Real Data or can be modeled based on your best guess

Ideally it would be different on a bulb position by bulb position basis


I would setup a cell for each individual bulb and then a cell for the Whole Unit which will get triggered if any fail


If you know the current life of each bulb you can use that as well


Then I would use a Data Table to run that 10,000 times and accumulate the results


If you have any other rules they can be added as the model is built


You will also need to decide what you want to collect as outputs from the model?


As Narayan hinted at above you should also decide on a set of words/terms for the project as Bulb, Unit, Cell seem to be used interchangeably and that may cause confusion.
 
Hi Narayan and Hui


Thank you for the interest so far. Your questions have raised some ideas that I need to digest. Certainly there seem to be more ways of looking at this question than I initially thought of. Please give me a day or two to mull over what I'm looking for and the ideas you have raised.


Andrew
 
Andrew


I would tackle this in a very similar fashion to one of the questions in last years 2012 Excel Modeloff competition.


If you visit: http://www.modeloff.com/questions/

and download the "Download Q&A" and "Worked Answers" for the MonteCarlo question

I think you will be 90% of the way to solving your problem


Happy to help out with the semantics of getting it to work
 
Hui


Thank you for the pointer. It looks like the direction I need. Will chew on it a bit and try applying what I learn to my case.
 
Andrew


I hope you have seen this post as well which describes some of the techniques used in the Modeloff example

http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/
 
Hui


Thank you for reminding me of this post. It is now printed out and I'm working through it. So far, the explanations and examples are making sense and are easy to follow.
 
You must log in or register to reply here.
Back
Top