Autosum many ranges quickly with Multi-select & ALT= [quick tip]

Autosum many ranges quickly with Multi-select & ALT= [quick tip]

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=

Continue »

Please join 50 ways to analyze data course to become an awesome analyst

Hi friends & readers of Chandoo.org,

I am very happy to invite you to our newest online class, 50 ways to analyze your data. This program makes you an awesome analyst, training you on vital skills like data analysis, data science, visualization, modeling business problems and finding best solutions.

Please click here to know more about this program & enroll.

What is this course?

50 Ways to analyze your data - an online course from Chandoo.org to make you a better analyst

It is the age of big data. Alas, what we need is big insights. But finding even small insights buried in our data is a hard task. To find the stories hidden in your data, you need to follow a process like this:

  1. Collect & clean data
  2. Structure the data
  3. Model business problems
  4. Analyze the data (or solve the problem)
  5. Visualize results
  6. Find conclusions
  7. Add layers of complexity to the problem
  8. Build what-if scenarios
  9. Reach conclusions
  10. Take action

This is where the 50 ways to analyze your data course helps. In this program, we analyze 50 familiar, important and diverse business situations using several of the above steps.

Continue »

Analyzing half a million complaints – Customer Satisfaction Scorecard [Part 3 of 3]

Analyzing half a million complaints – Customer Satisfaction Scorecard [Part 3 of 3]

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.

Continue »

Analyzing half a million customer complaints – Regional Trends [Part 2 of 3]

Analyzing half a million customer complaints – Regional Trends [Part 2 of 3]

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
Continue »

Analyzing half a million consumer complaints [Part 1 of 3]

Analyzing half a million consumer complaints [Part 1 of 3]

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.

Continue »

Formula Forensics 040 – Apportioning Sales by Criteria

Formula Forensics 040 – Apportioning Sales by Criteria

Lets look at how to apportion sales according to multiple criteria

Continue »

Not so wild lookups [video]

Not so wild lookups [video]

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

Continue »