All articles with 'minif' Tag

First a quick personal update: There has been a magnitude 7.8 earth quake in NZ on 14th November 2016 early morning. It is centered in Kaikoura, which is about 250 km away from Wellington. We did feel several shakes and after shocks. It has been an interesting and often scary experience. But my family is safe. I feel very sad for the all the damage and the loss for families in NZ. If you suffered from this quake, My prayers and thoughts are with you.

Yesterday, a friend asked me an interesting question. He has school distance data, like above. He wants to know which is the closest school for each school.

There are a few ways to answer this question. Let’s examine two approaches – formulas & pivot tables and see the merits of both.

Here is a quick Pivot table tip. Let’s say your work at ACME inc. requires some fancy pants analysis of product sales. Imagine looking at below data & trying to find out the earliest & latest date for each product sale.

Of course, we can concoct a version of MINIFS & MAXIFS to answer the question. But why bother, when you can answer the question with just a few clicks.

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.

Whenever we deal with large amounts of data, one of the goals for analysis is, How is this data distributed?

This is where a Box plot can help. According to Wikipedia, a box plot is a convenient way of graphically depicting groups of numerical data through their five-number summaries: the smallest observation (sample minimum), lower quartile (Q1), median (Q2), upper quartile (Q3), and largest observation (sample maximum)

Today, let us learn how to create a box plot using MS Excel. You can also download the example workbook to play with static & interactive versions of box plots.