Excel - Analyzing Filtered Set Of Data

I wanted to calculate the average and Standard deviation etc of a filtered set of data.

See the cell F4 in this sheet where the average of a set of cells is calculated with 2 filters. 

See cell H4 where Standard deviation is calculated on a filtered range and cell I4 calculating the same STD using the normal way.

(I got the solution from this site.)

If you want to count the count of cells having some text strings "abc" in them (Note that I say having a string and not cells having value "abc", meaning I am referring to cells having values "abcpqrs" also)
then use this formula countifs(a1:d5, "*abc*)

Use of sumproduct to calculate sum of certain cells across multiple columns from a rectangular array of rows and columns based on multiple filter conditions 

• Set on column headers and 
• set on values in other columns 

is shown in the same link in the "sumproduct" sheet, cell D9.

If you want sumproduct to include a case of pattern matching (substrings) then see cell E9 in the same sumproduct sheet.

Using Dynamic Sheets In Microsoft Excel

http://www.yarrcade.com/2009/06/16/excel-dynamic-worksheet-names-in-formulas/

http://www.ozgrid.com/News/oct-2006.htm

http://www.ozgrid.com/Excel/named-ranges.htm Name Manager is in Formulas tab.

Formula For Finding Maximum Value In Excel Subject To a Criteria In Another Column

=MAX(IF($B$20:$B$470=1,$E$20:$E$470))

Finds the max in column E from those rows where column B = 1.

I think the formula doesn't work if the values are in another sheet.

Additional reading:

• http://chandoo.org/wp/2009/01/28/100-excel-tips-resources-to-celebrate-2000-rss-subscribers/

Intelligence And Personality Disorder

People with BPD's (= Borderline Personality Disorder and NPD = Narcissistic PD) apparently have problems holding different (good, bad and ugly) views of another person and merging all of them into a single view by understanding that any person has all three aspects. Unfortunately, as per the article, people with such disorders see other things or people in black and white (all good or all bad).
https://www.psychologytoday.com/blog/stop-walking-eggshells/201112/black-and-white-thinking-is-both-bpd-and-npd-trait

Then there is this quote by F Scott Fitzgerald (FSF for short): "The test of a first-rate intelligence is the ability to hold two opposed ideas in mind at the same time and still retain the ability to function."

Does this mean that BPD's and NPD's in some ways aren't intelligent?

Given below is a quote by John Galt in Ayn Rand's Atlas Shrugged.

"To arrive at a contradiction is to confess an error in one’s thinking; to maintain a contradiction is to abdicate one’s mind and to evict oneself from the realm of reality."

Does this mean that, as per FSF, John Galt and Ayn Rand aren't intelligent? Or as per the definition of BPD, NPD, that the heroes of Ayn Rand and she herself have a black and white thinking and hence may be having a personality disorder of the kind mentioned at the top?

F (=Feeling people as in MBTI) people rarely see contradiction or think it is a cause to get upset about. Only F persons can hold contradictory views at the same time comfortably. Because they don't think in terms of rules. A contradiction is not an issue for an F. A person with T will get upset with a contradiction (as Ayn Rand's quote above shows). 

Contradiction is a term in the language for T (T=Thinking only). The issue with Ayn Rand is that she thinks that T is the only thing right and everything else be dammed.

Well, I can say one thing. If FSF is not F, then I am a BTSA. A friend of mine says this thing about the ability to hold contradictory views is more to do with J and P than with T and F. Meaning, she replaced my T with J and and my F with P.

The difference between what my friend says and what I say is that one of us thinks its a T vs F issue while the other thinks it's actually a J vs P issue (replace T by J and F by P). I have always suspected that T and J usually go together - meaning there is a good bond between the two as there is between F and P.

T (or J as my friend says) focuses on understanding things exactly and being right and correct and and not tolerating anything else. F (or P) focuses on pleasant and appreciating grades of grey and accepts and probably expects contradictions which adds to the vibrant shades. 

As a result TJ's usually are unpleasant while FP's are usually very pleasant to be with. The TJ's are usually better students than the FP's earlier in life. It's surprising that the latter are much more ambitious than the former. FP's usually are pleasure seeking and extremely averse to pain while TJ's are pain seeking and duty oriented while being averse to pleasure - themselves as well as for others. TJ's reflect the behavior of Saturn while FP's that of Venus. A person with a strong Saturn and a strong Venus will exhibit both TJ and FP traits. 

Exceptions:
I have come across exceptions. An uncle with strong Mars, Venus and Saturn has very few traits of Saturn except for being long lived. Same is the case with the mother of a friend of mine. Another friend who is largely a TJ was not a good student at school while her husband, an FP, was a good student.

Additional reading:

• http://vbala99.blogspot.com/2015/01/martin-luther-kings-quote-on-cowardice.html
• http://vbala99.blogspot.com/2014/10/einstfpj-mbti.html
• http://vbala99.blogspot.com/2015/03/feeling-or-thinking.html

Interesting Violence

Interesting Stuff

An approximate translation as requested by a friend:

There is a general belief that women are less strong than men. Hence the weapon that they use is harsh words. At some point, a man loses patience and ends up using physical violence (there do exist some women who retaliate). This helps women use the laws regarding marital violence. In these circumstances, the women who used the harsh words are not punished, not even cautioned. If we term the action of a man resorting to beating as violence, then the woman whose harsh words triggered the violence is also punishable.

In such court cases, children are used as armor. Children are used as weapons in order to blackmail the husband and his family. In order to avoid these, many husbands remain silent against their wives' verbal assault.

A child belongs to both parents. Is it fair that such women resort to false allegations against the men in court, take custody of the child and ask for maintenance of the child from the husband?

In India, the groom's family makes the bride's family miserable at the time of marriage. And the reverse happens at the time of divorce!

Women's nature and conditioning usually makes them very careful about leaving a trace of their actions. While a man's action (as in violence) has a legal repercussion and is morally incorrect, the woman's action would usually be such that it is not legally incorrect while it may be morally reprehensible. Men have a lot to learn from women.

As I had mentioned to a friend of mine a few years back, both men and women do similar things. In case of violence men, being stronger use the physical means. Women use non-physical means of aggression and trespassing which can be loosely termed as emotional violence. 

I am Reminded of a thirukkural:

தீயினால் சுட்டபுண் உள்ளாறும் ஆறாதே 

நாவினால் சுட்ட வடு.

Translation: The wound caused by fire may heal but that caused by words won't. - (More importantly words leave no trace and cannot be detected during autopsy)

Martin Luther King's Quote On Cowardice, Vanity, Expediency And Conscience

A friend sent me this quote. http://www.thekingcenter.org/blog/mlk-quote-week-conscience-asks-question. 

What is the basis of our actions? Cowardice, vanity, expediency or Conscience? If we talk about right or wrong (conscience), are your standards same as mine? 

When we claim to do things based primarily on our conscience, I don't think it's a very accurate statement. 

First, do we have a largely uniform set of standards? I guess we don't. What you think is right and what I think is right, may not be the same. We may not use the same yardstick. The yardstick for your conscience and mine may be different.

Our action is based on which question gives us most jitters. Am I doing a wrong thing, is what I am doing unsafe? Not popular or expedient enough? 

Conscience (an attribute of Saturn) and courage (an attribute of Mars) may not go together. These are two independent attributes. A person may have one or both or none of the two. A person with conscience and courage like MLK (Dr King) may stand up for what is right. A person with conscience and without courage may just silently suffer or lend a small hand.

Yes courage often leads to creativity. And creativity is not always a great thing. Hitler had tremendous creativity and courage. And so did Osama Bin Laden. 

People who prefer safety (weak Mars) as the primary guiding principle aren't bad. They lack courage or they may be contented with their lot. The risk of taking an initiative which could fail badly may be too much for them.

What is the difference between vanity and expediency? Vanity tries to ensure that one's actions are noticed by others. Expediency is about ensuring that one's goals are met and the action may not be tom tommed unlike in the case of vanity. 

Expedient people also have a lot of courage and initiative and creativity. What is the difference between working on the basis of your conscience and being expedient? In one case Right/Wrong (as one understands it) guides one's actions. In the case of expediency, it's the ambition (what's good for me, how to maximize what I get with the least risk, effort) that drives it. 

On the face of it, MLK's quote seemed to identify 4 simple choices that a man has!

Abstracting this, two people whose conscience, courage and ambition levels are very different will obviously have a difficult time getting along with each other.

Ellsworth Toohey

I have been trying to understand the character of ET in Fountainhead. This was one of the links that I read. 

Unfortunately most reviewers seem to write about ET somewhat along the same lines - that though very similar to Stalin in being communist / collectivist and power hungry, unlike him ET doesn't use physical power to achieve his ends. 

I am still waiting to read something that touches a cord. I am reminded of one incident that Ayn Rand writes about ET. That when Ellsworth, as a boy, won a debating contest other students felt that ET was given the easier side of the topic. So ET then talked for the other side and still won the contest. Now, what do you make of that?

And how does this relate to my dislike of any of the Tamil (frankly I don't think the dislike has anything to do with the language being Tamil) debates that is often shown on TV.

Additional reading:
http://vbala99.blogspot.com/2012/07/organ-malaise-head-vs-heart.html

Thriruvalluvar And Ann Landers

I came across this quote apparently by Ann Landers "The naked truth is always better than the best dressed lie". And that is in such contrast with the "kurals", especially the first two, in http://www.valaitamil.com/veracity-172.html.

Thiruvalluvar was a person who believed that truth is secondary to being nice. A well-intentioned lie was always preferred to a harsh truth. 

Now, why do Ann Landers and Thiruvalluvar say such opposite things? Landers must have had a strong Saturn and Thiruvalluvar, a strong Venus. Saturn is thoughtful, while Venus cares about maintaining image. Saturn lives in the abstract world of thought, while Venus is about the here and now sensual world. Saturn is about renunciation and seeking truth while Venus is about seeking material comforts and being diplomatic. 

Harischandra might have been another strong Saturn since he refused to lie no matter what it cost him and his family. 

I have always wondered what might have been the discussion been like if Harischandra and Thiruvalluvar ever happened to have a discussion.

Of course, there is no right or wrong. Each person leans towards one end of the truth - diplomacy continuum depending on what he is comfortable with.

J (as in MBTI) people are usually very touchy and not very pleasant while P's are the opposite. The latter are very ambitious. J is caused by Saturn while P is caused by Venus.

A friend sent me this link on Ti and Te (as in MBTI): http://www.celebritytypes.com/blog/2013/04/why-te-is-inductive-and-ti-is-deductive/. I guess I am Te.

Additional reading
https://vbala99.blogspot.com/2012/11/bad-venus.html

Controlling Behavior

Controlling behavior is usually indicative of a strong Mars in one's horoscope. I have come across such people. 

Recently some example from movies and books came to my mind of people who were so controlling that they moved from mere reporting of events (as in media) to creating the very spectacular events that planned to report.

The first is from the novel Godfather by Mario Puzo. While explaining the character of the Godfather, the author says that the former would solve problems for others which he (the godfather) had created in the first place. 

The next example I remember is the book "The almighty" by Irving Wallace. A media baron wants to beat his competitors by rushing in news before they do - only he starts creating the very news that he wants to report. A similar theme was in the James Bond movie Tomorrow Never Dies where Mr Carver plays a similar role as in "The Almighty". When the desire / ambition (caused by Venus) is strong and the energy levels are high (strong Mars), one usually ends up being extremely controlling and the next step being (as Mr. Hyde) to intentionally cause problems which the person solves or reports while playing the Jekyll role.

A person with this nature usually is great at gauging (1) exactly how much to push, how much pressure to apply for maximum results and when to withdraw - incidentally one has to only read about Hitler who managed to annex a few countries in Europe without firing a single shot - all by sheer force of speech and applying the right amount of pressure, blackmail (2) which mode of persuasion to use - the soft emotional approach, the brute force, the logical appeal - basically "sama dana beda dhandam". The ability to gauge people and choosing the right amount and kind of pressure is a feminine or Venusian trait. When Mars also is strong, it usually creates a character of immense force.

Such people are "emperors" and they treat other people as their subjects. They have an unusual ability to notice things around them. Every thing or person or event or word uttered is viewed by them with the sole objective of meeting their personal goal. And being emperors, they hate to see any emigration of their "subjects".

What they say may not often be the absolute truth. Truth, by itself, being of no relevance to them and is never a goal. Truth is useful only insofar as it is a parameter to be analyzed, manipulated and presented appropriately. Statement of unadulterated truth by such people is an option exercised only if it is seen as the best option. They are excellent propaganda machines, marketing and spamming comes as naturally to them as breathing air.

The words uttered by them are meant to sway the audience. They are usually eloquent and use 150 words which 5 might have sufficed. 

They believe that the packaging of message is as important as the content of the message. And while delivering a message they would think through multiple options of packaging before deciding the optimum one based on the nature audience, which they would have gauged fairly accurately. They would make excellent lawyers, but horrible judges.

Their enthusiasm, optimism, single minded focus towards their goal is amazing. One gets charged while being with them.

Varieties Of Intuition - MBTI: Ni And Ne

I have been intrigued by intuition, and the difference between extraverted and introverted varieties. 

After going through the definitions of N, and various articles I was sure that I was S and not N. A friend of mine would insist that I was N. 

I kept thinking.

Intuition is the ability to see patterns in data, the ability to see possibilities, the ability to intuit - "a ha, the butler did it (the murder)" rather than painstakingly go through all the clues to figure out who did it. 

As I reflected further, I realized that N had two distinct halves, I had one half and none of the other half. I could see pattern, I could sense a pattern, condense data but ,for the life of me, I could not see intuit (in the dictionary meaning of the English word).

I now understand the two halves this way. One part which goes downstream from data which perhaps is deduction (Ni). Another part which goes upstream and sees what could have caused the data (Ne). The latter is very close to what is generally known as feminine intuition.

If you look at the data here which shows the details of each MBTI type and statistics... you will see that there is a marked difference in the prevalence of Ti and Te (as dominant functions) between the genders. You can filter for the dominant function to be Ti and Te and see the sum of male percentages and sum for female percentages. 

Same for Fi and Fe across the genders. Now when we come to Ni and Ne, we find that male percentage of Ni is almost double of female percentage while female percentage for Ne is about 20% higher than male percentage.

Doesn't this seem to indicate that Ni goes with T and Ne with F? Meaning that the N/S and T/F are perhaps not independent dimensions. Look at the correlation between F/T and J/P. F constitute total (Male + Female) 60% of population, of which half is FJ and half FP. But if we look at T which constitute 40% of population, 24% is TJ and 16% is TP. But that is going to be a different post about interrelation between different parameters.

A good explanation of the difference between Ni and Ne is given here in the comment by sociotronics. Ni eliminates ideas to reach the one and only "best fit". 

A simple statement "I am fine" would be perhaps seen by Ni as just that. No other information or possibility could be derived. While an Ne could see what caused Jim to say "I am fine", that Jim perhaps wasn't feeling good when he said that, may be.... See the difference? When you take a data point and go downstream with it, sometimes you can't go anywhere because there is no pattern to be found, yet. But if you went upstream you could see a possibility. Ne is from the heart. Ni is from the head!

I am reminded of a maths problem about dividing 17 camels. Which of Ni or Ne could solve the problem? I think Ne.

Additional reading:
http://www.personalitypathways.com/education.html

Communication, Marketing And The Dreapta Foot Forward

I have been reading a few articles on what makes you look fat and how to care for your tresses. And I thought, wow.. These are the need of the hour for the modern woman. All said and done, if it's your bad hair day and if you look fat, what else you got... :(

Then I read about the rape of a woman by a Uber taxi driver in, where else?, Delhi. And Uber's response that the company carries no liability insofar as the behavior of the drivers are concerned. Any personal damages suffered by the passenger is to taken up with the driver and/or the owner. And not with Uber. And this is the same company till yesterday mentioned that it's the safest rides on the road. 

No, I am not saying Uber is any less safe than other taxis. Delhi is an awful place for any man, woman or child or cat. Nevertheless, when an entity that proclaims itself to be the safest and also says that it is not responsible for the safety of the passengers (only the driver / owner of the taxi is responsible), one wonders...

I am reminded of an advertisement for bharatmatrimony.com which I see on TV. "Hum unse milate hai jo aapko samjh paate hain"  - we connect you to such people who understand you

If a marriage with a partner you found on bharatmatrimony doesn't work, do you get to sue Bharatmatrimony? How different is a Bharatmatrimony from Uber? Both seem to claim things which they cant follow through ("safety" in the case of Uber and well, aaah ummm, connecting you with right partners in the case of Bharatmatrimony). 

Who are these people, companies who say all those cute words but are very sure that they will not be accountable for those words? How many other such people are there around us?

The point is that there are some assertions made by entities which sound very good but they will not put it on paper. How do you know which assertions belong to this kind?

Art Of Negotiation

I was watching Mahabharata today - the scene where Satyavati tells Bheeshma that she will not marry Bheeshma's father Shantanu because Bheeshma is the crown prince. That her children would never become the prince, they would only be lowly soldiers while he, Bheeshma, would go on to become the king and she could never accept that.

I wonder. She was a fisherwoman and the king was in love with her. And she said categorically that she would marry him only if her children could ascend the throne in place of Bheeshma.

What kind of a person was she? Such an ambition.

And I can't help thinking of Wallis Simpson who was in a similar position with Edward VIII (Uncle of Elizabeth who is the current queen) in England in late 1930's. Did she negotiate thus? 

Could Shantanu have abdicated in favor of Bheeshma and married Satyavati? What kind of people were Shantanu, Satyavati and Bheeshma and Satyavati's father?

A friend of mine showed me the Bengali version here. Satyavati apparently gave Shantanu two options, either give up his kingdom as Edward VIII had done or make her the queen and ensure that her children would become the prince in place of Bheeshma.

Well each version of Mahabharat tells the story with a different flavor. Which version depicts the real Satyavati or the real anyone in that epic for that matter? My comment on Satyavati above was with respect to the character I saw on Vijay TV showing a Tamil version. I use the article "a" and not "the" as I am not sure how many Tamil versions there are.

Focusing on the Bengali version, Satyavati gave 2 options. Either that Shantanu give up his kingdom or ensure that her children would go on to ascend the throne. While both the options were painful to Shantanu I myself perhaps look more kindly upon the first option. But is the 1st option really so much better than the 2nd? Does the 1st option make Satyavati's character much better?