Archive for February, 2016

Let’s say you have data in a worksheet in various ranges, and you want sum up each range at the bottom.

Something like this:

How to do all this one shot?

Simple. We use multi-select & ALT=

This is the final part of our series on how to analyze half a million customer complaints. Click below links to read part 1 & 2.

1. Complaint reason analysis – Part 1
2. Regional trends & analysis – Part 2

Customer satisfaction scorecard

In the previous parts of this case study, we understood what kind of complaints were made and where they came from (states). For the customer satisfaction scorecard, let’s focus on individual companies.

This is part two of our three part series on how to analyze half a million customer complaints. Read part 1 here.

Analyzing Regional Trends

As introduced in part 1, our complaints dataset has geographical information too. We know the state & zip code for each complaint. Please note that zip codes are partial or missing for a 10% of the data.

In this article, let’s explore three ways to analyze regional trends.

1. Regional trends by state, product & issue
2. Complaints per million by state
3. Complaints by zip code

How would you analyze data when you have lots of it? That is the inspiration for this series.

Let’s meet our data – Finance Industry Consumer Complaints

As part of open data initiatives, US government & Consumer Financial Protection Bureau maintain a list of all consumer complaints made against financial institutions (banks, credit unions etc.) You can download this data from the catalog page here. I have obtained the data on 1st of February, 2016. The download has 513,824 records. Each row contains one complaint.

In this and next two parts of the series, we are going to analyze these half a million complaints to find insights.

Lets look at how to apportion sales according to multiple criteria

In case, this is the first time you are hearing about Excel formula wildcards, check out the Using wildcards in Excel VLOOKUP formula tutorial.

So you know about wild cards like * ?, now how would you tell VLOOKUP to ignore them?

Say, you are genuinely interested in looking the value “* Payroll” in a lookup table. What then?

This is exactly the problem faced by Peter in our forum post VLOOKUP and cells with “*” NOT to be interpreted as wildcard

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 52nd session of Chandoo.org podcast, let’s discuss monkeys, Ok, I am kidding. We are going to talk about M is for Data Monkey book.

What is in this session?

In this podcast,

• Updates: Why so much gap between episodes?
• Quick introduction to Power Query
• Why you should get this book?
• What is in this book?
• A very cool example of the techniques you will learn
• Conclusions

Most advanced Excel users know that slicers are cool. Today, let’s learn how to use slicers to create an awesome selection mechanism for your dashboards and forms.

First see a quick demo

Looks slick, eh? Read on.