Y2K Spreadsheet excercise, try it and invite your polly friends to watch

greenspun.com : LUSENET : TimeBomb 2000 (Y2000) : One Thread

I tried a simple spreadsheet excercise that makes seeing believing. It is a simple probabilities, straight math excercise that have been done on more sophisticated levels, but this one is one you can do.

Make a column of 100 entries. If you're using Excel, then use the rand() function and generate 100 random numbers that represent 100 mission critical systems or suppliers of a fictional company. Set the boundaries of the random number from 0 to 100. Set up a cell to type in the probability of success of any one of the critical systems at 95%, thus establishing a 5% chance of any one of them failing in a seperate cell. Next, set up another parallel column that uses the if() command. ex. if(b1<5,1,0). Next, sum up the "if" column and if it adds up to 1 or more, then the company as a whole shuts doen. Next, keep updating the random numbers by retyping the 95% or 5% and you will be there forever trying to get a single iteration to pass the company. Play with 97,98,99, and 99.5% as well and you will be amazed at how high a probability of individual critical systems chance of succes can be and still bring down the company. 95% look very bad!!! I can email you my Excel spreadsheet if you request it, already set up. It could turn a Polly into a preparer.

-- James Chancellor, PE. (publicworks1@bluebonnet.net), October 13, 1999

Answers

Upon reflection, you should realize that the numbers your spreadsheet generates illustrates serious problems built into the assumptions in your formulas, rather than anything resembling real predictive value.

It should be clear that critical systems experience "failure" even more often than you plugged in, which is why we have an army of maintenance programmers. According to your spreadsheet results, our economic system shouldn't last 10 minutes! Yet not only does the economy march right along, it takes serious calamities in stride.

At the very least, your formulas embody two false assumptions -- that "failures" completely disable the critical system from doing anything at all, and that they are permanent. Real-life critical system failures tend to disable only one of many functions the system performs, and only for a short time.

So this is another example of 'proving' that the bumblebee can't fly. Too bad the bumblebee doesn't know it.

-- Flint (flintc@mindspring.com), October 13, 1999.


Flint,

You seem to be confused between the words "failure" and "breakdown." Please study the important difference between these two words. "failure" and "breakdown." Yes systems breakdown all the time. For example you may experience a nervous breakdown if your mind is unstable. If you experience brain failure you are dead.

Believe it or not companies do fail all the time. Many limp along but most crash and burn leaving a trail of debt and unemployment. Many other things fail too. Marriages, machines, partnerships, complex systems, civilizations.

Even whole economies fail - remember the Great Depression? Its a big scary world. Things do fail. People get hurt and die. Things fall apart and fail and cannot be fixed.

Here an even simpler example is this - If you fail a test you can't fix it.

Just think of Y2k as a test. If we fail to pass the test then we lose power, water supply, sewage treatment, food distribution. Does that help you understand?

Main Entry: 1fail

Pronunciation: 'fA(&)l

Function: verb

Etymology: Middle English failen, from Old French faillir, from (assumed) Vulgar Latin fallire, alteration of Latin fallere to deceive, disappoint

Date: 13th century

intransitive senses

1 a : to lose strength : WEAKEN b : to fade or die away c : to stop functioning

2 a : to fall short b : to be or become absent or inadequate c : to be unsuccessful (as in passing an examination) d : to become bankrupt or insolvent transitive senses

1 a : to disappoint the expectations or trust of b : to miss performing an expected service or function for

2 : to be deficient in : LACK

3 : to leave undone : NEGLECT

4 a : to be unsuccessful in passing (as a test) b : to grade (as a student) as not passing

- fail7ing7ly /'fA-li[ng]-lE/ adverb

-- R (riversoma@aol.com), October 13, 1999.


There is another spreadsheet exercise. mayb e even better, which you can conduct using the last two verisons of the Navy master Utitliy List. Pete Berry posted a thread a few days ago, called "The Column of Hidden 3's" in which he exposed a column -=- column "R" -- on the excel version of the MUL spreadheet posted by the Navy. Column has a width of "0" and must be widened to reveal a series of category "3" ratings giveng a dozen or more communities -- but not appearing on the published spreadsheet. If you can;t locate this inthe archives, go to Jim Lord's website, click on Forum, and find ti there under PBerry. Excellent hands on interactive demo of the ongoing double standard.

-- Roch Steinbach (rochsteinbach@excite.com), October 13, 1999.

Flint requisitioned some words and here they are:

"According to your spreadsheet results, our economic system shouldn't last 10 minutes!"

Flint is that exactly 10 minutes. If not, say 9 minutes and 59 seconds would that cause a failure of your prediction. Or if it were 10 minutes plus one second, would your statement fail. Or would it only appear to fail.

Quite vacillating.

-- bonehead (bonehead@bonehead.bonehead), October 13, 1999.


How many critical systems in a jet liner? Do you fly? Why?

-- flyboy (flying@isgreat.com), October 14, 1999.


As Flint pointed out, your arguments use a number of false assumptions. The main one being that use have assumed a failure in a critical sytem will automatically result in the failure of the company. Well I have just witnessed evidence that this assumtion is false.

I work for a Power Generator, and on tuesday morning at 02:16 am the SCADA failed. It crashed completely and would not even reboot. (A hardware issue). The controller on duty was unable to get the standby technician for more than three hours. Yet the result was NOTHING. No generators tripped, there was no reduction in power output. And when I went to work this morning the company was still in existance.

So, James, I do believe that your post would have a lot more credibility if you could acknowledge the fact rthat failures in systems can occur without causing catastrophe.

Malcolm

-- Malcolm Taylor (taylorm@es.co.nz), October 14, 1999.


Moderation questions? read the FAQ