This is Part 2 of the blog Let’s play with all the presidents (Excel)-Part1. Please read Part 1 there first to follow along. To find the zodiac sign of a president we need to map his month and day portion of his birthday to a table that map the month and day to a zodiac. […]
Tag: analytics
Let’s play with all the presidents (Excel)-Part1
Specifically, we’re going to start with the most basic data about US Presidents, and “play” with them to extract some more meaningful, interesting tidbits. Along the way, you’ll learn how to: Recognize, and deal with unprintable characters Shape raw (unruly string) data for analytics Find trends Make interactive charts and tables Use fuzzy logic and […]
Ranked, custom charts and animation (Excel)
In this blog, I share how to use simple data, and create some meaningful charts, even with animation and custom graphics. Along the way, I’ll demonstrate techniques to rank and spark some ideas on PowerPoint<->Excel interaction. Let’s start with the dataset. Following are the responses from a survey of 1,002 consumers from a couple of […]
Timelapse animation in Excel
Not long ago I demonstrated how to create and share animated charts using Excel (search for “soccer” or “excel” in the search box of my site). Here, I present a cool 3D chart created in Excel with various dimensions including time and geo-location. This animation is based on a dataset for power stations across different […]
Random Fun with Excel
This is a seemingly useless but visually fun usage of visuals, combined with Excel functions and VBA I did to take a break from my typical gnarly Excel analytics work 🙂 How it works: 1. Fill in a formula to generate random numbers between 1 and 100 (inclusive) across the cell range. 2. Apply 2 […]
A simple dataset with many questions (and answers!)
I’ll start with a simple dataset and demonstrate how many critical questions can arise just from this. You can now imagine in a real-world situation, how many more complex questions can be asked and information gleaned! But if we can’t extract the key answers from this table, we won’t be able to tackle even the […]
Simple & brilliant way to extract multiple matches in one cell
 The scenario: You have a dataset with values in one column that’s unique (or semi-unique) and another column with its associated values as Key:Value pairs but that column has repeating values. Imagine a list of members whose names are in one column and their corresponding membership status next to it as below: What we […]
How many real days…net? (Excel)
This is one of the simple and yet powerful tips when working with Dates in Excel. To calculate the number of workdays between two dates BUT EXCLUDING weekends (depending on your locale settings), we can use NETWORKDAYS(). In US and most western countries, Saturdays and Sundays would be excluded from the calculation. Without changing your […]
Sample Size in Python
This is part of a 3-part series on the topic. Please read the posts in the order for maximum clarity and context: 1. Sample Size and Margin of Errors. Everything you need to know and ++ 2. Sample Size (Contd.) 3. Sample size in Python (This one) Ok, after reading the first 2 posts, you […]
Sample Size (Contd.)
This is part of a 3-part series on the topic. Please read the posts in the order for maximum clarity and context: 1. Sample Size and Margin of Errors. Everything you need to know and ++ 2. Sample Size (Contd.) 3. Sample Size in Python In this blog, we’ll use actual numbers to determine […]