Analyzing 300,000 calls for help [case study]

Analyzing 300,000 calls for help [case study]

Over the weekend, I got an email from Mr. E, one of my students. Mr. E works at a police department in California and as part of his work, he was looking at calls received by police. Whenever police get a call for help, multiple teams can respond to the call and go to the location. All of these dispatches are recorded. So a single call can have several such dispatches. And Mr. E wanted to findout which team responded the first. The problem?

Finding the first responded team is tricky.

Today let’s take up this problem as a case study and understand various methods to solve it.  We are going to learn about writing better lookups, pivot tables, power pivot and optimization. Put on your helmets, cause this is going to be mind blowingly awesome.

Continue »

Weekend open mic – Share your one hand Excel shortcuts

Weekend open mic – Share your one hand Excel shortcuts

At Chandoo.org, we are big believers of keyboard shortcuts. There are several posts (1,2,3,4more and even more) discussing useful Excel shortcuts. Today I want to introduce a new kind of keyboard shortcuts. One hand shortcuts.

One hand shortcuts – Half the work, double the fun

The idea is simple. When you can use only one hand to complete the shortcut key presses, it is called as a one hand shortcut.

Continue »

Excel Links – Getting used to life in Windy Wellington Edition

So we moved to Wellington, New Zealand few weeks back (on 17th of July 2016, to be precise). After spending first 3 weeks in Jeff’s house and a hotel, we moved in to our rental home over the weekend (on 6th of August). Around the same time, the worst of Wellington winter waved welcome to us. We quickly learned how to stay warm indoors (layers, hot water bottles, rugs and more layers). Kids started going to school few days back and they are loving it. I bought a bike and managed to go out on few rides on the hilly roads of Wellington and found a strange for sale sign too.

For sale: Pony poo and pine cones

Anyhow, Since we didn’t have internet connection until today, I thought I will start by sharing a few Excel links with you. Check them out to get your fix of spreadsheets.

Read on…

Continue »

Find the seals [Excel puzzle]

Find the seals [Excel puzzle]

First a little back story:

Last Friday (on 29th of July), we (Jo, kids & I) went on a day walk to Red Rocks. It is a rugged coastal walk near Owhiro bay in Wellington. It was a windy & cold day. So why did we brave the elements of nature on this 10km walk? To see seals of course. And we did find a few of them. We also caught glimpses of snowy peaks in Southern Island of New Zealand.

3 Seals Excel Puzzle

Now I can’t take you on the same walk thru internet. There is no Excel function that can teleport you from your office (or home) to Owhiro bay. So I made the next best thing.

An Excel puzzle with 3 hidden seals.

Continue »

Correlation vs. Causation [Charting Chatter]

Correlation vs. Causation [Charting Chatter]

Here is a trap that is easy to fall in to. Confusing correlation as causation. As analysts, it is our job to see the data as it is rather than imply causation that doesn’t exist.

Let’s sample a chart, recently featured in Economist’s graphic detail under the title Measuring well-being.

Continue »

Add any number of days, months or years to a date with this simple trick

Add any number of days, months or years to a date with this simple trick

Let’s say you have a date in A1 and want to find out future date after 2 years, 4 months and 9 days.

Here are a few formulas you can try.

  1. =A1 + DATE(2,4,9)
  2. =EDATE(A1, 2*12+4) + 9
  3. =A1 + 2*365 + 4*30 + 9

Surprisingly, each formula gives a different result! So which one should you use?

Continue »

5 Shortcuts for you, one for every weekday [Awesome August]

5 Shortcuts for you, one for every weekday [Awesome August]

Let’s kick start our Awesome August 2016 with a simple but very useful set of shortcuts. 5 shortcuts, one for each weekday.

  1. Monday ALT + M N: Open name manager. Very handy, if you have quite a few range names and want to edit / manage them. Remember, this is a sequence shortcut, that means, you press ALT M, let go of both keys and then press N.
  2. Tuesday CTRL + T: Create a new table from data in current region. For more on tables, check out our Introduction to Excel Tables page.
  3. Wednesday CTRL + W: Close the current workbook, while keeping Excel open.
  4. Thursday ALT + T O: Opens Excel options. Very easy to remember too.
  5. Friday CTRL F: Fridays can be hard to concentrate. Use CTRL+F to find what you want in the current workbook. Use CTRL+H if you wish to do a find replace.
Continue »