**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.

We are re-opening enrollments in to the 50 ways to analyze data program in two weeks (on 7th of September, Wednesday). I want to share the process, techniques and visualizations you can use to analyze any business data with a case study. In this series of articles, let’s analyze fairly complex and large data set to derive insights.

**Please join our Analytics Email Newsletter to get all three parts of this series** and information about 50 Ways to Analyze Data program.

Since the original data shared by Mr. E is confidential, I made up some random call data in Excel. Here is a sample.

The calls data is maintained in an Excel table, named *calls. *

We have 288,625 rows in this table. Let’s examine the columns in detail.

- Call number – a unique identifier for the call
- Unit id – Name of unit responding to call
- Call time – Time stamp when we got the call. This is repeated for all rows with same call number
- Enroute DTS – Time stamp when unit left to attend call
- Onscene DTS – Time stamp when unit reached scene

We need to answer the question – **how soon are we helping the callers?**

This can be answered in a variety of ways.

- Calculate the earliest response time for each call by,
- difference between call time and earliest Onscene DTS
- difference between earliest Enroute DTS (ie first unit to respond) and corresponding Onscene DTS

- Analyzing the response times
- finding key statistics –
*ie*average, median, quartiles and outliers - Slicing the stats by month, call region (data not available) or team

- finding key statistics –

Mr. E’s email was about **how to calculate the earliest Enroute DTS and corresponding Onscene DTS **values.

In this article, we will explore ways to calculate these values using various Excel techniques. In the next article, we will analyze response times by various dimensions to understand the trends better.

We can use a variety of techniques to calculate the earliest enroute time.

- MIN IF formula to find the minimum time among all for a particular call number
- VLOOKUP exact match on sorted data table
- INDEX +
*approximate*MATCH on sorted data table - Pivot tables
- Power Pivot

Let’s review each of these techniques and see which ones are easy, fast and accurate (*ie* awesome)

This is where Mr. E got stuck and emailed me. He was implementing an array formula to fetch the earliest enroute time using MIN and IF formulas.

Assuming a call number is listed in cell **H4**, we can fetch earliest enroute time using below MINIF formula.

`=MIN(IF(calls[Call Number]=H4,calls[Enroute DTS]))`

While this formula is *technically *correct, Mr. E faced a curious problem. Not all units chose to attend a call even after taking it. This could be because they chose to go on another call or realized that another team is already on the way. So quite a few items in [Enroute DTS] are blanks *ie 0. *Now, because we are using MIN formula, it fetches 0 as the earliest Enroute DTS, *which is clearly a wrong answer.*

We can add an additional check to handle blank [Enroute DTS] entries.

`=MIN(IF((calls[Call Number]=H4)*(calls[Enroute DTS]>0),calls[Enroute DTS]))`

Now this works.

Related: Excel MAXIF & MINIF formulas – explained

**Verdict on MINIF formula approach**

While MINIF formula is a simple & elegant solution to this problem, it is **not scalable**. Mr. E had close to 300,000 calls spread among 400,000 rows in the calls table. This means when you want to calculate earliest Enroute DTS for all the 300,000 calls using MINIF, you can take a very looooong coffee break.

We can use VLOOKUP to return *first occurrence *of Enroute DTS for a given call number. We needed *earliest value *of Enroute DTS, not the *first value. *

Problem?

Well, what if we sort the calls table by call number and Enroute DTS?

Now, VLOOKUP should fetch earliest Enroute DTS.

`=VLOOKUP(M4,calls,4,FALSE)`

should fetch the earliest Enroute DTS for a call number in M4.

**Verdict on VLOOKUP approach**

While VLOOKUP is easier than MINIF and gives us the answer we want, it too is not scalable. On my sample data of 300,000 rows, the VLOOKUP approach took more than 2 minutes. On a production data set, this approach could mean disaster.

Why bother with *exact *lookups when an *approximate *one would do? That is logic behind this approach.

Assuming the call numbers are listed in M4 onwards,

- We find the first call’s earliest Enroute DTS using regular VLOOKUP method as mentioned above.
- From second call number onwards, we use an approximate MATCH method like below

`=INDEX(calls[Enroute DTS],MATCH(`

**M4**,calls[Call Number],1)+1)

Notice that we refer M4 to fetch earliest Enroute DTS for M5.

Related: INDEX formula – INDEX + MATCH formulas

**Verdict on INDEX + MATCH approach**

This approach is significantly faster than VLOOKUP or MINIF solution. On test runs with same dataset, this approach took *just a fraction of second. *That is roughly 100 times improvement in speed.

If you must use a formula approach, use this.

Pivot tables are a great way to answer questions like these. You can create a quick pivot to find earliest Enroute DTS for all calls by,

- Insert a pivot from the calls table
- Add call number & unit ID to row labels area
- Add Enroute DTS and Onscene DTS to values column
- Change calculation for these value fields to SUM (as by default dates are COUNTed)
- Sort the pivot by Enroute DTS – Oldest to newest
- Change the report layout to tabular view
- Go to value filter for Unit ID and filter by
**bottom 1 value by Enroute DTS** - Now you get a sparkly clean pivot with what we need.

**Verdict on Pivot Table approach**

This is the quickest of them all. Very simple and scalable upto a million rows (although when you have so many rows, Pivot calculations can be slow). I recommend that you use Pivot Table approach whenever you are solving problems like this.

While pivot table approach gives us the answer we want in a quick and efficient fashion, it has few limitations.

- Pivot tables cant work when you have more than a million calls
- Doing further analysis by slicing and dicing the data on various dimensions can be hard

This is where Power Pivot truly shines.

Related: Introduction to Excel Power Pivot.

Let’s build a Power Pivot model around the problem.

We load calls data in to PP as linked table (but in a production environment, you can directly hookup to database containing the data, thus making your workbook smaller)

Now create below measures.

Enroute Time:= min(calls[Enroute DTS])

Onscene Time:= min(calls[Onscene DTS])

Response Time:= [Onscene Time] – [Enroute Time]

*Additionally, we can also create Earliest Enroute Time measure as,*

Earliest Enroute Time:= calculate ([Enroute Time], all(calls[Unit ID]))

Now, add the first 3 measures to a pivot table. Place call number & unit id in row labels area. Set up tabular layout for the pivot and viola, our answer is ready.

So there you go.

**Verdict on Power Pivot approach:**

If you have Power Pivot on your Excel, use this method all the time. This allows you lots of flexibility. You can also build additional measures to analyze the data. We will see some of these ideas in next part of this article.

**Click here to download sample workbook** containing all these solutions. Play with various formulas and measures to learn more. Beware, some of the formulas can take lot of time to re-calculate.

The purpose of any analysis is to uncover patterns, information and decision pointers buried in data. So far, we just scratched the surface of this data set. In the next installment, let’s dig deeper and see meaningful trends.

After helping Mr. E, he sent me below email. This is one of the reasons why I run Chandoo.org. Everytime I read a testimonial like this, I get a big bright smile on my face.

I just wanted to say thank you very much. Although some of my columns are still calculating (holy smokes it takes a long time), the ones I’ve applied this solution to are working brilliantly.

You’ve helped me solve a very big problem for the xxxx county sheriff’s office in California; serving some 400k people. I thought that might be a nice feather to put in your hat.

Thank you E for emailing an interesting problem.

If you like this and want to learn more about data analytics, understand how our 50 ways to analyze data program can help you, sign up for our course newsletter. **Please click here**.

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.

**Why bother? **Well, many reasons.

- They are convenient
- You can use the other hand to control mouse
- You could use the other hand to drive

This is a open mic post. So go ahead and share your one hand shortcuts.

]]>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.

Leave it to Jon Peltier to unearth relative truthfulness of politician’s statements. Where I come from, almost all politicians lie. But apparantly, some politicians lie more than other, at least in USA. Jon shows us how to make a diverging stacked bar chart to understand the true lies.

**Drug overdoses data from connecticut, analyzed in Excel**

Drug overdose, how about Excel overdose? Spreadsheet Journalism, a new blog I started reading just today, shows how to analyze publicly available data sources to uncover interesting stories. Abbot Katz does a fine job of mixing Excel with data journalism. Give it a read.

Debra shows us how to hide worksheets by color of the tab. This can be useful if you work with a massive workbook that has tons of tabs and want to hide all calculations worksheets or sensitive data tabs before emailing the file to entire department.

Enjoy reading them. See you soon with more Excel stuff.

]]>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.

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.

You have to use your creative skills to unlock the seals.

So what are you waiting for? **Download the find seals workbook** and get hunting.

After you find the seals, post your answers (or hints) in the comments section.

Excel puzzles are lot of fun. Check out below links if you want more challenges.

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

At the end of the article they show the above chart and say, (emphasis mine)

BCG also compared financial inclusion (the percentage of individuals aged 15 or over with a bank account) against each country’s SEDA score, revealing a clear relationship.The report’s authors found that

countries with higher financial inclusion generally had higher well-beingthan their peers at a similar income level.

Now, it is easy to misunderstand above chart and come to a conclusion that * if you provide bank accounts to everyone in a country, then the country’s SEDA score (well-being) would improve*.

But this would be wrong.

As an analyst leaning towards awesomeness, you want to ask questions like,

- Is it a coincidence that countries with higher well-being naturally have bank accounts for everyone?
- How does having a bank account impact a person’s well being?
- What other factors can contribute to well being? What about access to education, healthcare and information?

So there you go. Next time you see data or visual analysis that exhibits correlations, do not confuse them as causation. Question the factors first.

- How to select right chart for your data
- Don’t make your charts heavier than they should be
- Avoid data dumps in your charts
- Understand data to ink ratio to make better charts

Have you seen such misinterpretation of data in your work life? What was it like? *Please share your thoughts in the comments section.*

Here are a few formulas you can try.

- =A1 + DATE(2,4,9)
- =EDATE(A1, 2*12+4) + 9
- =A1 + 2*365 + 4*30 + 9

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

Let’s test them with a sample date to see the results.

Assuming A1 has today’s date, *ie *2nd of August, 2016, we get below results respectively.

- 10-NOV-2018
- 11-DEC-2018
- 9-DEC-2018

But which one is the correct answer?

We can use manual calculation to find the correct answer.

Today is 2nd of August 2016, so:

- Adding 2 years to it, we get 2nd of August 2018
- Adding 4 months, we get 2nd of December 2018
- Adding 9 days, we get 11th of December 2018

That means, the correct formula would be **=EDATE(A1, 2*12 + 4) + 9**

My preference is to use EDATE() when doing any date arithmetic that involves months or years. For adding either days, I use simple date + number method. For adding workdays, I use either WORKDAY() or WORKDAY.INTL() formulas.

**What about you? **What formula would you use to add any number of years, months and days to a give date? Please share your formulas in the comment section.

If you and Excel are always on a bad date, you could use some advice. Check out below tutorials to have an amazing dating scene.

- Find the last day of any month with this simple trick
- 42 tips for Excel time travelers
- How to highlight over due items in Excel
- How many Mondays between 2 given dates?
- Calculate Friday the 13ths in an year
*More tips on date & time calculations in Excel*

*This post is part of our Awesome August Excel Festival.*

*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.*Tuesday***CTRL + T:**Create a new table from data in current region. For more on tables, check out our Introduction to Excel Tables page.*Wednesday***CTRL + W:**Close the current workbook, while keeping Excel open.*Thursday***ALT + T O:**Opens Excel options. Very easy to remember too.*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.

*Monday***ALT + M N:**Opens 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.*Tuesday***CTRL + T:**Create a new table from data in current region. For more on tables, check out our Introduction to Excel Tables page.*Wednesday***CTRL + W:**Close the current workbook, while keeping Excel open.*Thursday***ALT + T O:**Opens Excel options. Very easy to remember too.*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.

*Bonus for weekends – ***CTRL+S: **Save you work before you vanish for the weekend using the CTRL+S shortcut.

That is all for now. If you are looking for more shortcuts, check out our comprehensive Excel keyboard shortcut list.

**What are your favorite Excel shortcuts?**

Tell me and rest of our readers what are your most favorite and useful Excel shortcuts. Post them in the comments section.

*This post is part of our Awesome August Excel Festival.*