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 .

The tasks are:
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.

Other Useful Links:

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

rktalwar.jpg (10618 bytes)Passing Away of a Legend - An obituary by N. Sivaraman & R. Vishwanathan:  The Hindu (May 13,2002)

Talwar — a saviour of SSIs By K. T. Jagannathan The Hindu ( April 26,2002)
R. K. Talwar: A banker ahead of his times: S. Parthasarathy
BusinessLine (May 07,2002)
Remembering Talwar: A. Chandramouliswaran Business Line (May 10,2002)

Sri. R.K. Talwar(1922-2002) was a legendary Banker. He was widely  known and respected for his honesty and uprightness.In 1981, when I was undergoing 2nd Branch training as a PO at Pondicherry, I had the good fortune of  meeting him. He had no airs about him. He asked me how I liked the Bank job and wished me success in my career.
He was an ardent devotee of Sri. Aurobindo and The Mother. He passed away in Pondicherry in 2002.
These Webpages aim to collect and present  impressions of Sri.. Talwar at a single place.
Contributions from persons knowing Sri. Talwar, having his photographs or any written material  welcome. Copies only need be sent. Archival material like old issues of Colleague, any other Circle House Magazines having articles by/on him  may also be sent. Persons having photographs can also send them by scanning them as .jpeg files
All  used contributions will be acknowledged in these pages.
Maintained by : R.S.Pai, Chief Manager (IT-Internet Banking-STEPS, SBI) (Note:Page was created in 2003 when I was working in SBI) 




Passing away of a legend  by N. Sivaram & R. Viswanathan
"HONESTY IS the best protection'' is the message that greets everyone in the lift lobbies of the Mumbai Corporate Office of State Bank of India. It was painted at the instance of R. K. Talwar, (RKT) who was the Chairman of the bank at the time the building was commissioned. RKT adopted honesty as an abiding principle of his life, but was wise to recognise that many others would try to be honest only if it were a protection and not policy. In his passing away, the country and its banking system has lost a titan.
RKT presided over the destiny of SBI during a truly historic moment in Indian banking. Until 1969, SBI was the only bank owned by the Government and had enjoyed a unique status in the minds of people; it was the biggest of them all. By the nationalisation of 14 other big banks in 1969, the uniqueness of SBI was eroded and the other banks offered stiff competition to SBI. By his foresighted and value-based leadership — he was the Chairman till 1976 — RKT placed SBI in such an unassailable position that it holds the top position even 25 years after he left the bank. The true measure of this achievement could be understood if one were to compare the progress of the other big banks; the biggest private sector bank, just before nationalisation, has been now shunted to a far lower place in the pecking order and the position of No. 2 bank after SBI is constantly changing.
In any mention about RKT, his steadfast faith in wholesome values would come on top. Two instances come to mind. When it was indicated in one of the advertisements of SBI that it was safe to bank with the bank, a competitor complained that it might give the impression that other banks are unsafe institutions; RKT immediately appreciated the point and ordered the withdrawal of the advertisement. Another was during the mid-1980s, when the then Chairman of the bank was talking to a leading international banker in a foreign country. The SBI Chairman was not sure whether the foreigner had heard of SBI: the latter put the former at ease, saying that he well knew of SBI, as the biggest Indian bank, "that can do no wrong". This compliment was earned by SBI only because of the legacy left behind by RKT.
RKT's foresight was exceptional. It was he who saw that the organisational structure and systems of the bank needed a thorough overhaul in 1970 and commissioned consultants to study the matter and suggest suitable corrective action. This was particularly needed as SBI's network of branches grew in geometric proportions from around 250 in 1955 (with a 150 years existence) to 650 in 1960 and over 8,000 in the next 20 years. He chose two brilliant academicians from the Indian Institute of Management, Ahmedabad, as consultants, in preference to foreign firms. His main brief to them was that the bank had "lost its branch managers and they have to be brought back to the main stream.'' That he, as Chairman, could empathise with the front line staff speaks volumes of his commitment to the organisation. The reorganised structure that he put in place had verily stood the test of time and, but for some modifications, was in place for well over two decades.
Men of vision are generally uncomfortable poring over details and usually leave the tiresome job to their glorified assistants. Not so, RKT. He was totally comfortable with detailed analyses of any complex problem and would go to the core of the issue immediately. And, the best part of it was that any formidable office note did not stay in his table overnight. The imprint of his incisive brain was quite visible in the perceptive comments that he made on the note. He was truly a man who clearly "saw both the trees and the whole forest.''
RKT was also readily accessible to all the people working in the bank and he did not take offence at certain remarks at which lesser mortals would have taken serious exception. An instance was when he visited the bank's staff college in Hyderabad and addressed a gathering of newly recruited probationary officers. One impish officer asked him as to what qualities made him Chairman of the bank. RKT politely replied that there was nothing extraordinary in him, but devotion to duty and the blessings of the Divine.
RKT trusted people and groomed all those working under him to shoulder higher responsibilities. He used to tell his trusted subordinates that they could well exercise up to his powers, in an emergency and report to him for ratification later. Even if the decision taken by the subordinate proved to be wrong, RKT stood by the decision. By demanding perfection from his people, RKT ensured that they became professionally strong.
In business development, RKT's contribution was phenomenal. He joined the then Imperial Bank in the 1940s when the bank gave loans only to the "reputed'' business houses in the country and little, if any, scrutiny of the client's financials and other factors was done.
He insisted on proper examination of the factual financial position of all the big borrowers and did not go by their mere connections or past reputation. Much before the Tandon Committee prescribed norms for financial analysis of big borrowers, at the instance of RKT, systems were in place in SBI to ensure that the bank's funds were put to proper use. In fact, almost alone among his peers then, RKT was quite comfortable with studying in depth, financial data of borrowers.
Lest one conclude that RKT was pro big business, it has to be recorded that it was he who was instrumental in giving a big thrust to financing small scale industries and businesses. He chaired a committee in the 1960s which recommended far reaching changes in the way SBI (or for that matter any other bank in India) gave loans to the smaller entrepreneurs. SBI, under RKT's leadership, proclaimed that "any activity that can produce salable goods'' can and should be supported with need-based bank finance. It was a severe cultural change from security based lending to need based lending and but for RKT's missionary zeal in promoting the cause of smaller units, it is doubtful if SBI would have assimilated the policy and implemented it.
Above all, he had the courage of his convictions and did not succumb to unreasonable pressures from external sources. On one occasion, he pleaded in the Chairman's speech in the annual meeting of SBI for increasing the interest rates on deposits, during a time when these were fully dictated by the Reserve Bank of India, which is the central bank of the country, besides being the owner of SBI. He insisted on doing so, despite a clear signal that the RBI Governor would like the matter to be deleted from the speech.
The final denouement came when the powers that be in New Delhi ordered him orally to give more money to an already sick borrower. As "obeying'' that order would have put SBI's funds in jeopardy, RKT refused, point blank, and it is recorded history that this decision of his cost him his job as Chairman of SBI . India would rarely see the likes of him who, at a fairly young age of 54, gave up the biggest job in commercial banking, to uphold a principle.
An ardent devotee of Sage Aurobindo and the Mother, RKT was fully committed in words and deeds to truth and honesty. Raj Kumar Talwar was not a mere prince, but a mighty Emperor among honourable men. May his soul rest in peace.


Talwar — a saviour of SSIs
By K. T. Jagannathan

CHENNAI APRIL 25. A super banker of yester-year with a clear fix on tomorrow. That was R.K. Talwar. The former Chairman of State Bank of India may have passed into the pages of history. Yet, his contributions, nay works, at SBI hold a lesson or two to young bankers of the coming generations. The introduction of organisational reforms and innovative banking in the 70s had brought out the visionary in Talwar. If the organisational revamp had underscored his concern for making banking a lot more creative activity, his concept of innovative banking had always in mind a proper blend of business goal and social obligations. Not surprisingly, SBI, during his tenure at the helm, was making some unorthodox lending such as financing prisoners, rehabilitation of ex-convicts, helping industrial workers to buy bicycles and the like. More than anyone, Chennaites will cherish his memory more. At a young age, Talwar was vested with the job of Deputy Secretary and Treasurer to Madras Circle. The five-year stay in Chennai saw him take the circle to the top in SSI lending. For Talwar, "small man'' had become some sort of on obsession. So much so, he was often referred to as "Saviour of SSIs,'' "Father of SSI movement'' and what not. He was singularly responsible for establishment of the merchant banking division in SBI. His `emotional involvement' with small units had forced him to direct this division to help grown SSIs graduate to next phase. Talwar laid much store by sound lending. Availability of security - even in the form of Government uarantee - in his reckoning, should never deviate a banker from the principles of sound lending.
Talwar always sought only a supplementary role for banks vis-a-vis assisting the farm sector. On several occasions, he had made it plain that the primary role still belonged to the co-operative sector. Unlike the new generation banker who does not hesitate to pull out of a firm at the first sight of trouble, Mr. Talwar had always held that it was the primary responsibility of the banker to identify the trouble and nurse a unit out of sickness.




R. K. Talwar: A banker ahead of his times by S. Parthasarathy
THE passing away of R. K. Talwar marks the end of a distinct era in Indian banking. The Hindu notes that he was the `Father of SSI (Small Scale Industries)'. But Talwar's contributions to the banking industry cannot be limited to SSIs. The impact he made was more sweeping, had become the benchmark of the banking industry's coming of age in the 1960s and the 1970s.
The business world is familiar with Talwar's important role in the flow of bank credit to SSIs; but less known is the fact that he brought about a sea change in the attitude of the banking system to SSIs. The banking system in the 1960s was filled with primitive attitudes to managerial accountability. Talwar saw this as a major barrier to the development of SSIs through banking support and boldly came out with the decision that, in the area of SSI financing, so long as the officers/managers were not guilty of mala fides, and so long as the appraisal of the credit was in order, the officers/managers would not be held accountable for errors of judgment. The result was amazing. SBI's performance in the development sector of credit was leagues ahead of all the other banks.
Talwar laid great stress on proper and comprehensive credit appraisal. The beginnings of a credit rating system were also founded through his initiatives. He asked the credit officers to make unambiguous recommendations. Fairly convinced that the appraisal system was the best safety net he could think of, Talwar astonished the corporate world by offering to surrender guarantees of managing agents and/or directors in lieu of a fair readjusting (upwards) of the lending rate.
Another important area in which Talwar's innovative approach fetched profitable results for SBI was the issue of pricing of products and services. Until the early 1970s, there was no scientific basis in SBI for pricing products and services. For instance, the rate of exchange (commission) for discounting a trade bill or a cheque and for collecting the same instrument was the same.
The fee structure completely overlooked the fact that while discounting an instrument, the bank was laying down funds upfront and while collecting the same instrument, the bank was enjoying the float — the very opposite. Talwar introduced more rational and rigorous pricing models and attitudes. These sweeping changes did not spare even the Government. SBI was then the sole agent of the RBI for conducting the treasury business of the Central and State governments. For this service, the RBI paid a compensation that did not meet even a fraction of the cost. Talwar raised this issue with the RBI and persuaded it to accept a third party assessment. Thakur, a leading private chartered accountant (who was to become a Minster of State for Finance in later years), was appointed for this purpose. As a result, the structure of compensation for SBI for conducting government business was placed on a rational footing.
When human resource development was still a relatively unknown management jargon, Talwar propagated the idea of human and social capital. He took personal interest in officers who he thought had the potential to grow fast; he called them for discussions, often over the heads of their superiors who were also involved in the matters discussed.
Talwar's tenure as SBI Chairman was also coincidentally a period when the trade union movement was getting to be aggressive and confrontational. This was particularly true of the unions in the banking system. The bank saw quite a few confrontations, strikes and agitations, as a consequence.
Talwar's equations with the RBI were always excellent. Because of his strikingly original and thought-provoking policy insights, he was always consulted by the RBI's top brass when it formulated important banking policy. Talwar was the SBI's first technocrat Chairman. Prior to him, SBI/IBI Chairmen were invariably appointed either from the ICS/IAS cadre or from the top management of the RBI. He, therefore, had to prove himself and demonstrate the stuff of which bank managers were made of.
He retired with his wife to Sri Aurobindo Ashram to lead a simple and quiet life. When the Ashram was getting to be a bit uncomfortable with internal squabbles, he moved out to a small sparse portion in a street house in Pondicherry.
(The author, a former managing director of SBI's Overseas Operations, is now adviser to Apeejay Group of companies.)




Remembering Talwar by A Chandramouliswaran
`A banker ahead of his times' (Business Line, May 7,2002) brings out clearly why Talwar was a much-admired figure in the banking circles.
The article does not, however, touch upon one important aspect of his personality.
Besides being a man of character and integrity, Talwar did not yield to political pressure and did not hesitate to give up his positions as Chairman of State Bank of India and, later, as Chairman of Industrial Development Bank of India when he could not agree to certain courses of action on the basis of his conscience.
I recall Talwar (in 1988) not showing any interest in giving the RBI his bio-data so that he could be considered for appointment to its central board.
Perhaps he came to the conclusion that he may not be able to perform his duties on the board of the RBI with the fierce independence with which he was used to performing his duties.
All those really interested in the subject of autonomy of the central bank should note this.
A. Chandramouliswaran
Former Executive Director, RBI

Thursday, 10 May 2012

Type in English and create Hindi/Kannada/Marathi Documents in Word

Here's an easy way to type in English and create documents in Hindi/Kannada/Marathi (transliteration) in MS-Word.  An advantage of this method is no font installation or additional software (other than Word and Google) are required. The receiver too can read it in Word without need for any additional fonts installation at his end.

(Government Departments insist on  correspondence in the official language of the State.Public Notices are displayed in Offices/Hospitals etc in multiple languages for public convenience or on account of the Official Languages Policy. For such requirements too this howto will be useful.)

1. Go to http://www.google.com/transliterate


2. In the language selection box (see red rectangle above), select the language (e.g. Hindi). Some other languages available are : Kannada, Marathi, Telugu, Tamil, Malayalam, Gujarati, Bengali etc.

3.  For example, to get
in Hindi, you need to type Mujhe Bangalore jaana hai 
in the space below the language selection box in the Google Transliteration website. 

4.  Type mujhe and press the space key. On pressing the space key, mujhe is tranliterated into Hindi. Type all the four words and you get something like this:

(Notice that Bangalore (in Hindi) has not been typed correctly.) 



5. To rectify the spelling, click in the middle of the second word and you get spelling variations (see below):
Click on the correct spelling variation needed (4th word in the displayed list) and the original word is replaced. 
(Note: Try variations in English to get the other language word right (E.g Bangalore, Bengalore etc)
6. The above text is just an example. You can type several sentences and paragraphs. You can also transliterate into two languages simultaneously by selecting a different languages before typing: 


6. Next open a blank Word document.  Select all the text in the Google transliteration website, then right-click and click on Copy.  Click in the Word document, right-click and click on Paste.  The pasted text may look like this:

7. In the Word Document, press Ctrl + a (to select all text). Then, in the Home Tab, select the Arial Unicode MS font (see below). 



7. The text should now be correctly displayed as shown below. Save the document for printing or sharing. 




















Tuesday, 8 May 2012

Bring Your Own Device (BYOD) and Blackberry Balance

Employees nowadays bring their own Mobile devices to the office and use them to access company resources like email, files and programs. This trend is called "Bring Your Own Device".  In such cases, the company saves the money that would have been spent on providing mobile devices to the employees, while the employees get to use their preferred mobile technology.

What appears to be a win-win situation is not so for the following reasons:
Companies struggle to manage and secure business information residing in/accessible through the phones.

To address these concerns, Blackberry has recently introduced Blackberry Balance ("work-life balance") technology.

Using this technology, IT Departments can set business rules to prevent copying of business information. Business data will not be share-able in personal apps and any attempt at violation will be detected and the employee notified. When the employee leaves the company, the IT Department will be able to remotely wipe all business information in the phone. Similar action can be taken when the phone is lost.

The announcement on the Blackberry website:


BlackBerry® Balance™ technology enables BlackBerry smartphones and tablets to be used for business and personal purposes without compromise.

Boost job satisfaction by supporting employee-owned smartphones
Offer greater BlackBerry smartphone choice flexibility to your workforce while retaining full control over business data security
Allow use of personal apps while restricting copy-and-paste functionality to prevent business information from being shared
http://us.blackberry.com/business/software/balance/








Saturday, 5 May 2012

Filling up blank cells in Excel

Compare the two screenshots below:


Sales data ( as shown in the picture at the Left) is entered in an Excel sheet.  The problem with this type of data entry is that Excel functions like SUMIF or COUNTIF will not work correctly. SUMIF, SUMIFs, etc are useful for finding the sales of a particular Salesman, say Govind, from the entire list of data.  For that we need data like the one at the right. So how do we quickly/automatically fill up the blanks?

Steps:
Fill up a sheet with the incomplete data as shown above.
( "+" in the steps below indicates "and")
1. Click any cell with data in it. Example, B5 with "Govind"
2. Press Ctrl + * keys to select the block of data. (If you are pressing the * (asterisk) key in the QWERTY part of the keyboard, then press Ctrl + Shift + *), This will select the entire block of data.
3. Press Ctrl + g (g can be capital or small).  Go To box will appear.
4. Click on the "Special " button
5. Next, click on radio (option) button: "Blanks"
6. Click on Ok.
7. Press the = key
8. Press the ↑ (up arrow) key
9. Press the Ctrl + Enter keys.
The blank cells will be filled with the value in the cell above.
10. Select the entire block again.(See step 2 above.)
11. Press Ctrl + C to copy
12. Click on Paste Values (in the Home Tab-->Clipboard Group) - see graphic below

(Note: "Paste Values" is done because the blank cells are filled up using formulas and unless the formulas are replaced with values they can mess things up when the data is sorted, etc.).

Note:
If the dates in Column A are not displayed correctly and are displayed as numbers: Click cell A2 which has the correctly formatted date. Next click on the Format Painter  (in the Home Tab-->Clipboard Group)(see graphic below). A paint brush symbol will be attached to the mouse.
Drag the mouse over the cells with dates in Column A to apply the correct format.








Friday, 18 November 2011

Excel How to find difference between two dates (in years/months/days)

How many ( complete ) years are there between 15-Oct-1983 to 19-Jan-2010?

In Excel:
In cell A2 input 15-Oct-1983 or 15/Oct/1983
In cell B2 input 19-Jan-2010 or 19/Jan/2010
In cell C2 type the formula =DATEDIF(A2,B2,"y")


Notes: 
The "y" in the formula can be in upper or lower case. It SHOULD be in DOUBLE quotes. 
The function DATEDIF can be entered in lower case of upper case but should be spelt correctly. 

Hint for months and days:
To get difference in completed months, replace "y" with "m" in the formula
To get difference in completed days, replace "y" with "d" in the formula

Note 1: If we use DATEDIF to calculate the number of days between 15-Mar-11 and 18-Mar-11 using "d" as the third argument in the formula, the answer will be 3 (i.e. it will exclude the end day). If we intend to include the end day in the calculation, then we should use the formula =DATEDIF(A2,B2,"d") + 1
 

Note 2: From Excel 2007 onward, the DATEDIF function is built into Excel. For Excel 2003 and earlier, go to Tools-->Add Ins--> Analysis Tool Pack --> OK to enable the function. 




Wednesday, 28 September 2011

Current date and time : Excel keyboard shortcuts

(Note: "Ctrl + ;" should be read as "Ctrl" key and ";" key pressed together

Press Ctrl + ; (semi-colon) to input the current date in a cell. The input date will be the current system date and will not change.
Press Ctrl + Shift + ; (semi-colon) to input the current time in a cell. The input time will be the current system time and will not change.
(To input both Current date and time in a cell: - Ctrl + ; (then input a space using the keyboard space bar) followed by Ctrl +Shift +;

Depending on the requirements, you can also use functions as below:
Current date (dynamically changing):
In a cell input =Today() and press Enter.  This will input the current date in the cell. As the date is based on the function TODAY, unlike the keyboard shortcut above, this date will change when the system date changes, for instance, when you open the Excel file again on another day later. 
Current time (dynamically changing) :
In a cell input =Now() and press Enter. This will input the current time (along with the current date) in the cell. As the time is based on the function NOW, unlike the keyboard shortcut above, this time will change when the system time changes, for instance, a few minutes later. 

Hint: Use TODAY and NOW in Excel Sheets which are used for printing reports. They will automatically update and show the current date / time 



Thursday, 22 September 2011

Excel : Keyboard shortcut for AutoSum

Summing (Totaling)  is a frequent task in Excel.

In the picture below, we have a block of values in column H and wish to get the total in a cell  below the last value.


In a cell below the block , press Alt and = keys together followed by Enter.  This is called  AutoSum. This is also available as a Tool button :

Note: We can do this for a block of cells in a row too by using the above keyboard shortcut in a cell to the right of a row block.

How it works? Excel checks if there is a continuous group of cells with numbers above (column block) , it then checks to the left (row block) and displays the formula. Before pressing Enter, we can check the displayed formula and manually change it in case Excel has not guessed it right.