Excel, Outlook, Windows Automation AND Understanding Human Behavior and Thought

How to track proceedings (arguments/orders etc) in Indian Courts in Real time (Live) in Twitter

2020-11-09T14:12:00.002+05:30

Go to Google Play, search for Twitter App, install it and open it

Once you have logged in to Twitter, you will see All Tweets with the latest appearing at the top. To refresh the tweets, swipe downwards on the Smart Phone screen.

For Tweets of proceedings in Indian Courts:

Press the search button (Magnifying glass) at the bottom of the app (see picture below)

In the screen box, search for "Live Law" .In the search results, press on the Live Law site. (see markings in picture below.)

Tweets from Live Law will be displayed. (If you are seeing promotional material from their website instead of the Tweets screen below, please press the LiveLaw icon (in a small circle)).

As earlier you will have to swipe down to see the tweets.

You may see various courts tweets mixed up and can focus on the tweets of your interest by checking the hastags of the tweet eg. #PrashantBhushan.

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

2017-05-05T21:09:00.002+05:30

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.

Fish Rain in Thailand and Einstein on Stupidity

2017-04-28T14:09:00.001+05:30

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:

http://www.thedailypedia.com/2015/04/fish-poured-like-rain-thailand-miracle-warning-from-god/

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.

Nothing Suceeds Like Success - Guarding against Survivorship Bias

2017-03-29T09:35:00.000+05:30

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.

Freakanomics Blog on Survivorship Bias

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

2017-03-29T09:01:00.000+05:30

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

	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

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

2012-05-10T17:55:00.000+05:30

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.

Bring Your Own Device (BYOD) and Blackberry Balance

2012-05-08T09:17:00.001+05:30

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/

Filling up blank cells in Excel

2012-05-05T11:17:00.000+05:30

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.

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

2011-11-18T11:29:00.000+05:30

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.

Current date and time : Excel keyboard shortcuts

2011-09-28T07:12:00.000+05:30

(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

Excel : Keyboard shortcut for AutoSum

2011-09-22T08:51:00.000+05:30

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.

Dates in Excel - Getting them right !

2011-09-20T10:05:00.000+05:30

In our daily work, we need to work with dates in Excel. Doing it the right way not only makes the task easy, but also ensures input of correct data. Once dates are correctly entered, we can find the number of days between two dates. This is necessary for interest calculation, fees calculation, etc. We can also calculate totals for a range of dates in case of sales data, group dates to get sales totals for a month and so on.

Suppose we input 05/12/10 in an Excel cell. Excel may retain that format or it may change the display to something else. The behaviour is often puzzling. Has Excel understood the date as 5th December 2010 (as I intended) or has Excel interpreted it as 12th May 2010 which will certainly result in wrong calculations? How do I enter dates as dd-mm-yy as we do in India? (while at the same time ensuring that my friend Bob in the US sees them as mm-dd-yy as they do there)

How do Excel dates work and how can we ensure better control over dates?

First step: Getting the dates settings right in Windows.
Windows Regional Settings (Windows XP):
Go to Start -->Control Panel --> Regional and Date Settings. In the dialog box (see below), select English (United Kingdom) and click on Customize...:

On clicking Customize you will see the following dialog box:. (Ensure that you click on the Date tab at the top of the dialog box to make it the active tab):

Click on the down arrow to the right of the Short Date format and select dd-MM-yyyy format

Click on the down arrow to the right of the Date Separator and select from . (period/full stop) or - (hyphen) or / (slash) . You are indicating to windows that you will be using this as the date separator while inputting dates. Please select your preferred separator.

Click on the down arrow to the right of the Long Date format and select dd MMMM yyyy

Click on OK, OK and come out of the Control Panel.

Once we modified settings as above and assuming the date separator selected is "-" (hyphen), we can now input "10-05-10" and be sure that Excel has understood it as 10th May 2010.

(To verify this, select the cell with the date and press Ctrl + Shift + #

The date will now be displayed (formatted) as 10-May-10 or 10/May/10 depending on the date separator setting.)

How are two digit years interpreted? If we input a two digit year from 00 to 29, Excel will prefix it with 20. Thus 10 becomes 2010, 15 becomes 2015, 05 becomes 2005 and so on. If we input a date from 30 to 99, Excel will prefix it with 19. Thus 56 becomes 1956, 31 becomes 1931, 99 becomes 1999 and so on. If we intend to input 2030, we will need to input it as a four digit year i.e. 2030. Similarly, if we intend to input 1910, we will need to input it as 1910 and not 10 because 10 will be converted to 2010. (This is because of the settings in the Date tab of Control Panel - see graphic above).

Also, note that although we have input the date in the dd-mm-yy format as above, the person to whom we mail an Excel workbook will read the date in format as in his machine. So 10th May 2010 which is displayed as 10-05-2010 in my machine will be displayed as 05/10/2010 in his machine if his Regional Date settings are mm/dd/yyyy

Important Note: Excel stores dates internally as numbers. This is the reason why a date is correctly displayed as either 10-05-10 or 05/10/10 in machines with different date settings. Display of dates can be controlled through formatting which will be discussed separately.

(The above settings in Control Panel need to be made only once in a PC. If you are not able to do this in an office environment, it could be because you are not logged in with administrator rights. In which case, you need to contact the System Adminsitrator for help).

Weekday of a date in Excel

2011-09-19T08:34:00.001+05:30

What day of the week was 15-Aug-1976? Or 26-Jan-2015?

In Excel:
In Cell A2 (or any other cell) input the date as 15-aug-76 or 15/aug/76
In cell B2,input the formula =TEXT(A2,"ddd"). The weekday will be displayed.

Note: If instead of "ddd" , "dddd" is used in the formula, then the date will be displayed as "Sunday".

Quick Economics - Petrol price hike - how it works.

2011-09-16T09:29:00.007+05:30

Assume that the price of one litre of crude oil (from which Petrol is made) is one US Dollar (USD). Also, assume that an USD (US Dollar) can be purchased from a Bank for Rs. 42. This means the cost of a litre of crude oil is Rs. 42/-. After adding other input costs, processing costs, overheads, taxes, profit margins, etc., the selling price of Petrol would be determined.

Scenario 1: The price of crude oil remains stable but the rupee weakens: Assume that over a period of time the rupee weakens (i.e. you need to pay more rupees to buy one unit of a foreign currency, in this case the USD) and the price of a USD has risen from Rs. 42 to Rs. 56. This means that for a litre of crude oil you need to pay Rs. 56 now as against Rs. 42 earlier. Other things remaining the same, the selling price of Petrol is bound to go up.

Scenario 2: The price of crude oil goes up but the rupee remains stable:. Assume that the seller abroad has hiked the price of crude oil to USD 1.33 per litre. Now you have to pay the rupee equivalent of USD 1.33 i.e. Rs. 56 to buy a litre of crude oil. Other things remaining the same, the selling price of Petrol is bound to go up.

We can conclude that the price of imported goods/services are bound to go up when the price of the foreign currency goes up or there is a hike in the price of the foreign goods/services.

A weak rupee and impact on exporters. The above scenario covered importers, people who pay in Foreign exchange. What about exporters (IT Companies/BPOs, manufacturersm etc), people who receive money in Foreign currency for goods / services sold ? In the above example, these people will benefit from a weak rupee as they will be able to get Rs. 56 now as against Rs. 40 earlier for each USD that they will convert into Rupees.

(The above is a simplistic basic explanation. The prices are also imaginary and are for illustrative purposes only. )

Gmail : Quick way to find Mail/Chat conversations

2011-07-01T07:13:00.001+05:30

Here's a quick way to find all mail/chat conversations with a particular contact in Gmail.
Let's say, I want to find all mails exchanged with Bhau Ghadge. I proceed as follows:

Step1: In the left pane in Gmail, look for the box for searching contacts:

Step 2: In the box, start typing the contact name. Once the name is displayed, move the mouse to the name and then to the right, locate the menu "View Recent Conversations" and click.

Recent mails/conversations will be listed in the right pane.

How SBI's Green Channel Banking works..

2011-06-30T13:51:00.000+05:30

State Bank of India (SBI) launched Green Channel Banking (paperless Banking) at select branches some time back. SBI's Personal Banking Branch at Goa-ves Belgaum is now providing the service to its customers.

Here's how it works:

A POS (Point of Sale) machine (similar to the one seen at Shopping Malls) has been placed at desks. The customer swipes his Debit Card and is offered three choices: Cash Withdrawal, Cash Deposit and Funds Transfer.

Using the Cash Withdrawal option, he can withdraw more Cash than he can at the ATM. Also, unlike the ATM, here he can choose the denomination of notes.Similarly, he can deposit Cash using the Cash Deposit option.

I tried out the Funds Transfer option. On selecting the option, I was asked to input the beneficiary's Account number. The system showed me the beneficiary's name and asked me to input the amount to be remitted. I was also asked to authenticate the transaction by inputting my Debit Card PIN(Personal Identification Number). On successful authentication, a message popped-up on the Bank official's screen. On his confirming the transaction, the transaction was completed and a receipt issued showing all details of the transaction.

There are no charges for use of the facility.

About Me

2011-06-18T07:12:00.001+05:30

I am currently ( 2017) working as a Sr. Principal Consultant at Atyati Technologies Pvt. Ltd (formerly a Genpact Company) a company engaged in providing solutions for Financial Inclusion and Micro Finance. My work description includes Business Analysis, Compliance, Quality Assurance, Reconciliation, Automation, Documentation, Anomalous transactions detection by Data analysis etc

I worked as Banking and Finance Domain Consultant at Oracle Financial Services Software Ltd. Mumbai (Formerly, iflex Solutions) in the Software Quality Assurance Department.My work areas included: Functional Specifications Reviews, Test Plan Reviews, Error Analysis, Documentation Quality Audit, Risk Management, Bid Profitability reviews, etc. I conducted several Advanced Excel Training Programs in the Company.

Prior to that, I was Chief Manager (IT-Internet Banking) at State Bank of India (SBI)’s Global IT Centre at Mumbai. i was in-charge of Technical Support, Security, Vendor Management, Contracts, Training aspects of Internet Banking in State Bank of India and its 7 associate Banks. I also won the “Best Trainer” award in SBI at all India Level in 2002 for my contribution to popularizing use of IT in the Bank during his stint as Faculty Member at the SBI’s Staff Training Centre at Bangalore. I conducted MS-Excel related trainings of different levels during his tenure at SBI. I worked with SBI from 1980 to 2007 earning rich experience in various capacities in varied Banking Domains: Foreign Exchange, Branch Administration, Retail Lending, Commercial Lending, Agricultural Lending, SME Lending, Personnel (HR),Training, IT (Internet Banking)

Advanced Excel Training/Consultancy at:

Oracle Financial Services Software, Pvt. Ltd. Goregaon, Mumbai

State Bank of India, Staff Training Centre, Bangalore

State Bank of India, Internet Banking Department, Belapur, Navi Mumbai

Institute of Management Education and Research (IMER), Belgaum.

Reynolds Chemequip Pvt. Ltd. Belgaum

N.G.E.F (Hubli) Ltd. ,Hubli

HLL Healthcare Ltd, Kanagala Belgaum

Sterling Tapes Pvt. Ltd. Belgaum

BTP Structural (India) Pvt. Ltd. Belgaum

Vayavya Labs. Pvt. Ltd., Bangalore/Belgaum

Noorani Corbel Developers Pvt. Ltd. ,Belgaum

Malwan Beach Project, G.S.S. College, Belgaum

Jain Yuvak Mandal’s Educational Institutions, Belgaum

Belgaum Cancer Hospital, Belgaum

Sahyadri School (Krishnamurti Foundation India), Tiwai Hills, Near Rajgurunagar, Pune

Weekend Excel Course for office-goers and professionals a IMER, Belgaum

Other Assignments:

Visiting Faculty at Institute of Management Education and Research, Belgaum

Also I lecture on Finance, Banking at various colleges and institutions.

Contact details: Rajendra Pai : Mobile: 94835 19900 Email: rspai9@gmail.com

My previous website with Useful Links