The beauty of Excel really is the number of unusual things this tool can do.  When people write the history of analytics and research in the years to come, it won’t surprise us if it is divided into two distinct aeons – B.E- Before Excel and A.E- After Excel.(and boy, are we glad that we are working in the enlightened A.E aeon!)

Here are 3 lesser known functions we have found useful on their day. There are many other similar ones,  that we have never used before. If you know any such , share them in the comments below!

1. INFO – Have you ever walked into the client site and been handed a brand new computer? Not sure how to use it, you breathe a sigh of relief when you notice Excel and quickly begin work there. But try as you might, you can’t access the computer’s control panel or properties to find out more about the machine. Let Excel help you. Want to check how much = total memory is available for use on the system? Click on any cell and type in ‘=INFO(“totmem”). Not sure which OS version you are working on? Try ‘=INFO(“osversion”). And once open, don’t know where this file is from? Try ‘=INFO(“directory”)’

2. Date and Timestamps- Working on a long financial model over many days and weeks- and want to remember when you last corrected each row? Just go to the end of the row and Click CTRL +; The day’s Date will be stamped right there. Move to the next column and click CTRL+SHIFT+; the current system time will be stamped. This function is also very useful for teams working in different shifts. Ask your evening shift colleague to leave this time stamp on every row he tampers with. Come in next morning, and you know that all the rows with timestamps after 6:00 PM have not been changed by you.

3.  Custom Views: You know how it is. You are creating a detailed budget plan, with many assumptions, some of which you don’t necessarily want your boss to see. You have that giant ugly worksheet with a million calculations, 5 different regions, and  a hundred assumptions that you have created. Then you hide some rows and columns, format some others, size the page up into a single- page print layout and print this version for him. “Great work”, he says, and asks you to share just the Kolkata figures with your Kolkata team mate. You filter the relevant information, take another print out and are just about to fax it when you notice an error in the calculations.

Don’t fret. Use Custom Views. Click on View>Custom View> and Create one View called Boss which has only the rows that you want to show your Boss, and another called Kolkata, created after filtering for the region. Work on the entire unhidden, unfiltered sheet and whenever you need to rush for a print out, just select the relevant custom view!

Hoor, our Bangalore Excel trainer, shares some little known text functions with you

FIXED Function:

The FIXED function rounds a value to specified precision and then converts the rounded value to text. The function uses the following syntax:

=FIXED (number, decimals, no _commas)

The number argument supplies the value that you want to round and convert to text. The optional decimals argument tells Excel how many places to the right of the decimal point that you want to round. The optional no_commas argument needs to be either 1 (if you want commas) or 0 (if you don’t want commas) in the returned text.

For example, to round to a whole number and convert to text the value 1234.56789, use the following formula:

=FIXED (1234.56789,0,1)

The function returns the text 1,235.

REPT function:

The REPT function repeats a text string. The function uses the following syntax:

=REPT (text, number _ times)

The text argument either supplies the text string or references the cell holding the text string. The number_times argument tells Excel how many times you want to repeat the text. For example, the following formula:

=REPT(“Bora”,2”)

Returns the text string BoraBora.

VALUE function

The VALUE function converts a text string that looks like a value to a value.

The function uses the following syntax:

=VALUE (text)

The text argument either supplies the text string that you want to convert or it references the cell holding the text string. For example, to convert the text string $123,456.78 — assume that this isn’t a value but a text string you can use the following formula:

=VALUE (“$123,456.78”)

The function returns the value 123456.78.

How TV Ratings Work

January 15, 2010

In light of all the brouhaha about the late night shows in the US, it would be interesting to understand how television ratings really work.

The company Nielsen that conducts statistical research began operations in 1923 to sell engineering performance surveys.  The company soon moved on to market research eventually expanding its repertoire to include a national radio rating survey (yes, those were the days). The company eventually moved on to television rating in the 1950’s and has formulated the analysis pattern used by TV rating agencies across the world now.

The key component of the Nielsen ratings is, of course, statistical sampling. Most of what we refer to as television rating comes from a little box called the daily meter which captures what channel the household logs in to and the  People Meter which captures which members of the household watch the show. The all-inclusive number of households  participating in Nielsen daily meter system each year  25,000 out of a grand total of more than 110 million American households that own a Television Set. In addition, Nielsen also sends out millions of paper diaries during November, February, May and July (called “sweeps” in the industry parlance) for households to enter all viewing decisions during these periods.

A combination of these two data sets is used to create TV ratings. A TV rating of 8.5/12 means that 8.5% of the total households watched that show and that 12% of the households who were watching TV at that time watched that show. The numbers that most networks look at though, are the rating in the 18-49 age group as (presumably) that’s where the big spenders lie.

And how do we measure television ratings in India? The process is very simple. Sample households across India’s 75 largest towns are given the  People Meter to capture their viewing decisions. This sample, of course, comes from the estimated 130  million Indian households that have television. The company that conducts this research in India is an industry body called TAM (Television Audience Measurement)

The key issue in case of both of these ratings is obviously the sampling decision. Are these sample sizes enough? Do they accurately cover all ethnicities and economic sections? In countries with vast economic and cultural diversities like India and US can any sample size except for the absolute population be representative? A key part of both TAM and Nielsen’s strategy, therefore is sample design. And while these ratings may not be perfect, they ARE representative. If there’s one thing our many years in the Analytics Industry has taught us, is that some information-however little-is ALWAYS better than no information.

The problem however is the lack of options. For better or for worse, with minor changes, the Nielsen way of doing things has been the established industry norm largely because the huge size and cost of this endeavour. With media spends on television advertising increasing by the day, and television viewing patterns altering dramatically with the introduction of videos on the internet and the DVR, it is only time before someone comes up with a better algorithm to tell us what programs on television are really being watched by most people.

Websites We Love: Econtalk

December 20, 2009

The Library of Economics and Liberty has put together an excellent collection of talks by major economists and statisticians on myriad topics on this website.  Host Russ Roberts talks to featured guests, professors, authors, and Nobel Prize winners about the economics behind current events, markets, the Great Depression, free trade, and the curiosities of everyday decision-making and New talks are updated almost every week, and while you may not always agree with the experts , it is always fun to listen to them. Some of our favorites are Nassem Taleb on the current financial crisis (http://www.econtalk.org/archives/2009/03/taleb_on_the_fi.html),  The great Milton Friedman himself on Money (http://www.econtalk.org/archives/2006/08/milton_friedman.html) and the always entertaining Michael Lewis on the Economics of Sports (http://www.econtalk.org/archives/_featuring/michael_lewis/) Add a couple of these talks to your I Pod and get an excellent mental and physical work out the next time you go for a walk.

Pay special attention to the related links beneath each talk for additional related reading and the comments that (like everywhere else on the internet, occasionally get lively! Pay a visit to the website right now, and we promise you won’t be disappointed!

Excel Excels!

December 18, 2009

Interesting article last week about the Top 10 Trends in Analytics, Business Intelligence and Performance Management for 2010 here:

http://www.enterpriseirregulars.com/5706/the-top-10-trends-for-2010-in-analytics-business-intelligence-and-performance-management/

Over the next few weeks we will be exploring each of these trends in greater detail for our student community here.  But the one that interests all of us at ATI most is the prediction that Excel will continue to provide the dominant paradigm for end-user BI consumption. With the launch of Excel 2010 with enhanced charting, collaborative and conditional formatting features, Excel is more powerful than ever before. The BI world can truly be divided in to B.E (before Excel) and A.E (after Excel) – and you don’t want to know how companies managed before Excel!!

Excel has many features that even old hands don’t know about, and fidgeting with its ‘Help’ feature is as good a way as any to spend a quiet day in the office.

For today, we leave you with this tiny featurette that we can bet most of you didn’t know about. Did you know that you could remove duplicates from a long list of entries using the Advanced Filter feature?

Just click on Data > Filter> Advanced Filter and select the column with duplicated entries in List Range. Select Copy to Another Location and click on ‘Unique Records Only’. Now, go to “Copy to” and select the starting point where you would like to see your unduplicated list.

And voila!

You don’t even need to sort the rows before doing this!

If you have any other interesting Excel Tricks that you would like to share with us, write to us. We are always willing to learn!!