## Friday, 5 May 2017

### Conditional Minimum, Maximum, Average, Maximum below Average etc using Excel

In G2 to G17 and H2 to H17 of an Excel sheet the data as shown in the picture below is recorded. Column G contains the category of vehicles sold over several months. Column H contains the corresponding count (quantity) of the category sold. (Although the data is sorted below for visual examination, it need not be sorted for the purpose of the task.). In reality the data could cover several unsorted categories (not just two as below ) spread over several months/showrooms .

1.  to find out minimum and maximum quantity of categories (cars ,scooters) using the entire data set.
2.  to find the average of the categories
3. to find the minimum quantity of cars above the average of cars (the quantity next to the average above the average)
4. to find the maximum quantity of cars below the average of cars (the quantity next to the average below the average)

Please input the data as above in the cells in Columns G and H . Then, input the formulas as shown below in Column L (The formulas to be input are displayed in adjacent cells in Column M in the picture below.) After entering the formulas, press Ctrl + Shift + Enter if the formula type is Array (see Column J). If formula type is Regular, just press Enter. (If you did not press Ctrl + Shift + Enter while entering, press F2 in the cell and  then press the Ctrl+Shift +Enter; there is no need to type the Formulas again.) If you are going to type the formulas below, instead of selecting the range of cells, please note to name the Sheet as "Sheet1"

The learning here is that Excel has ready formulas for conditional Averages (arithmetic mean) . But for more complex computation, we have to depend on Array Formulas using a combination of existing formulas.

Note: If the Array formula is entered correctly using Ctrl + Shift + Enter together, the formula will be enclosed in braces ({}) as shown in the picture below (formula shown is in L2) . The braces are not to be manually input.  If not with braces, the result displayed may be incorrect.

## Friday, 28 April 2017

### Fish Rain in Thailand and Einstein on Stupidity

Earlier today I read a WhatsApp post containing photos of Fish raining on Thailand  roads. (I later discovered that this was a 2017 regurgitation of the original 2015 post.)

The "fish on roads" photos had provoked thoughts of "evolution,  miracle, warnings from God etc" in the mind of the person who had posted it in WhatsApp.

Pursuing my hunch that this was a cheap hoax, I searched the Internet and came across this link which explains the context of the Fish Rain hoax:

The article above further provoked me to wonder about human stupidity. I recalled this pearl of an observation by Einstein quoted by Fritz Perls, a great Gestalt Psychologist, whose books I read in my early twenties.

"Two things are infinite, the universe and human stupidity, and I am not yet completely sure about the universe." - Einstein as quoted by Fritz Perls

Just like Adam Smith's famous "invisible hand" that maximises overall human welfare,counter-intuitvely using selfish human behavior, the Internet has spawned its own "invisible universal brain" that takes no chances with human stupidity and ensures its infiniteness by constantly circulating such hoaxes.

In case you are getting the feeling that I am early vaccinated case, let me reassure you that no vaccine has yet been discovered against human stupidity. (While others use Whatsapp, I have my own playfields.) Also, given that Mankind has more urgent, pressing priorities, it is unlikely that any attempts will be made to discover such vaccine till eternity. In addition, we hold the late Mr. Einstein in such high regard that we will do everything humanly possible to ensure that he is not proved wrong.

The late Amos Tversky, who would have won the 2002 Nobel Prize in Economics with Daniel Kahnemann, his long time collaborator, but for his untimely death in 1996, was once asked about Artificial Intelligence. He replied, "We study Natural Stupidity". Studying stupidity can be a gainful occupation.

## Wednesday, 29 March 2017

### Nothing Suceeds Like Success - Guarding against Survivorship Bias

Nothing Suceeds Like Success - Guarding against Survivorship Bias

After winning a toughly poised cricket match, a news reporter asks one of the players about the defining moment that made a difference between winning and losing. The player responds: "It was a tense time and our morale was down. Royal Challengers had everything in their favour. Nothing could be easier for them than scoring 25 runs in 20 balls with 4 wickets on hand. Our team went into a huddle and the captain exhorted us, 'Let's make it, boys. Let's do it. '. And we got charged with energy, bowled, fielded tight and won the match."

On reading the story, many team leaders in areas as diverse as cricket and business are tempted to replicate the success story by getting into similar huddle meetings at moments of crisis.  (In fact, most positive thinking and motivational literature is replete with such prescriptions.) But, in most cases, such remedies do not work. Why?

Conclusions (as in case of effectiveness of cricket team huddles above) based on incomplete stories (data) can be erroneous. Had the news reporter asked the player, "Was this the first occasion that the captain gave such a pep talk?", the answer would likely have been, "No. He has done so in the past also. But on the previous occasions some other factors caused our defeat."

So, leaders who arrive at conclusions based on incomplete data and conclude that pep talk is "the" factor responsible for success, should be aware that pep talk is one of the factors and that there are other factors, including random ones, that make a difference.

So, What is Survivorship bias?

Survivorship bias is a faulty thought process  "that occurs when someone tries to make a decision based on past successes, while ignoring past failures." (Source RationalWiki). Being aware of the Survivor bias helps us keep it in mind while making decisions.

A famous example of Survivorship bias illustrates how millions of dollars were saved in the US during World War II by not making what could have turned out into a foolish  and expensive decision. The wrong decision could have changed the course of the war.

During World War II, a military Think Tank, consisting of the some of the finest strategic thinkers in the US was tasked to suggest measures to make planes stronger so that they could survive attacks when they flew inside enemy territory on bombing missions.

The Think Tank studied planes that had flown deep into enemy territory and had successfully returned to find patterns and identify attributes that made these planes "successful". They discovered that some areas in the plane bodies had plenty of shots and other had none. So, the obvious answer was to strengthen the areas having shot marks.

The rest of the Think tank agreed with this solution except for one of its members, Abraham Wald. Abraham Wald, a mathematician from Hungary, had fled to US during World War II.  He reasoned that the planes they had studied were ones that had got shot and survived. He therefore deduced that the areas that needed strengthening were not the areas with plenty of shots, because they were proven to be strong by the very fact that the planes had survived. The areas to be strengthened were the areas where the surviving planes did not have shot marks. The planes had survived because the enemy had missed shooting those areas. Hence, the areas without shots were the ones that needed strengthening. (Source : David McRaney article on Survivorship Bias)

The majority thinking in the above story is an example of Survivorship bias. And is one of the causes of wrong decisions. As one can well imagine, implementing the "obvious" solution would have meant spending millions of dollars on strengthening areas which needed no strengthening. And also, being none the wiser as planes would have continued to be shot down because the vulnerable areas continued to remain vulnerable.

Michael Shermer on Survivorship Bias in The Scientific American :  This post has a reference to Gary Smith's book "Standard Deviations" which says that 20 of the 35 publicly traded companies listed in the "In Search of Excellence" have done worse than the market average. Also, a Venture Capitalist's views on the subject.

Meera Lee's article on Survivorship Bias : The gambler example, a detailed discussion of Abraham Wald story as well as a reference to the "College dropout" as a wrong defining characteristic of Bill Gates and Steve Jobs success.

Karen E Klein's article in Bloomberg : Here she talks about Bill Gate's favorite Business book by John Brooks, "Business Adventures" about which Gates said: "Brooks didn’t boil his work down into pat how-to lessons or simplistic explanations for success,"

### A WEB PAGE IN MEMORY OF SRI. R.K. TALWAR

A WEB PAGE IN MEMORY OF SRI. R.K. TALWAR
Former Chairman, State Bank of India