All articles in 'Learn Excel' Category

Finding the closest school [formula vs. pivot table approach]

Published on Nov 18, 2016 in Excel Howtos, Learn Excel, Pivot Tables & Charts
Finding the closest school [formula vs. pivot table approach]

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.

Continue »

Can you solve this blood pressure problem? [IF Formula Homework]

Published on Nov 4, 2016 in Formula Challenges, Learn Excel
Can you solve this blood pressure problem? [IF Formula Homework]

Over on Facebook, Kristin asks, Help, my blood pressure is going thru the roof. I can’t seem to solve this blood pressure problem. 

Let’s simplify Kristin’s problem.

You have some data in the format shown above.

And you want to find out the BP category for each reading, using some rules. Read on to solve the problem.

Continue »

How many formulas should you learn? [Weekend Poll]

Published on Oct 20, 2016 in Learn Excel

Over at twitter, @for_the_moves asks,

That got me thinking. How many functions should you care to learn?

Continue »

Finding if a cell has 7 in it… [Pattern matching in Excel]

Published on Oct 18, 2016 in Learn Excel
Finding if a cell has 7 in it… [Pattern matching in Excel]

Imagine you work at MI5 as a HR officer. You want to find all agents who have license to kill (licence 7). Your data looks like above.

How would you go about it? 

If you filter the list or use FIND() or SEARCH() formulas, you will end up with agents who also have licenses 77, 17 or not7. So how would you solve this problem?

Of course, you do what any smart person does. You summon Excel and ask it nicely by using some wicked pattern matching logic.

Continue »

Check if a range has all numbers from 1 to n [Homework]

Published on Sep 30, 2016 in Excel Challenges, Learn Excel
Check if a range has all numbers from 1 to n [Homework]

Finally, spring weather showed up in Wellington this week. We cashed it as much as possible by going on treks, cycling trips, more treks and of course doing laundry.

Anyways, I don’t have time to blog. I must go out and help kids with some cycling. But I want to keep you busy this weekend. So here is a fun homework problem.

Does my range have all numbers from 1 to n?

Let’s say you have a range called range (duh!).  And you want to check if range has all the numbers 1 to n (say n=5) in it, each number appearing only once (no more, no less). You can assume the named ranges range and in your formulas.

See above examples to understand the problem.

So go ahead and post your formulas in the comments section. I will sneak in whenever I can to look at all your creative answers.

Continue »

Announcing 50 ways to analyze data course to make you an awesome analyst – We are open

Published on Sep 7, 2016 in Analytics, Learn Excel

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 »

18 ways to turn analysis projects into a nightmare

Published on Sep 5, 2016 in Analytics, Learn Excel
18 ways to turn analysis projects into a nightmare

Every week, we read news about failed analysis projects. If you listen carefully, you can hear the grunts, screams and curses of thousands of analysts all over the world about their analysis nightmares.

At Chandoo.org, we talk a lot about best practices for data analytics. So today, let’s peek in to the dark side and understand the mistakes that can turn your analysis project into a nightmare.

There are 3 parts in any analysis project

To understand these worst practices in analysis world, first let’s break analysis projects in to 3 parts.

  • Requirements
  • Data Structure
  • Tools & Construction

Let’s deep dive in to each area of the analysis projects to see what can go wrong.

Continue »

PSA: Don’t let auto correct spoil your party

Published on Aug 26, 2016 in Learn Excel
PSA: Don’t let auto correct spoil your party

So here is a news from strange but true department. Microsoft Excel blamed for gene study errors [bbc.com].

Microsoft’s Excel has been blamed for errors in academic papers on genomics.
Researchers trying to raise awareness of the issue claim that the spreadsheet software automatically converts the names of certain genes into dates.
Gene symbols like SEPT2 (Septin 2) were found to be altered to “September 2”.

Aah, classic!

This is what happens when you spend countless hours learning genome sequencing and very little about the software tools where your data goes. May be we need clippy back to warn people about such sticky situations.

Continue »

Analyzing 300,000 calls for help [case study]

Published on Aug 24, 2016 in Analytics, Learn Excel, Pivot Tables & Charts, Power Pivot
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 »

Excel Links – Getting used to life in Windy Wellington Edition

Published on Aug 10, 2016 in excel links, personal

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 »

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

Published on Aug 2, 2016 in Excel Howtos, Learn Excel
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]

Published on Aug 1, 2016 in Keyboard Shortcuts, Learn Excel
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 »

Find out how many times a value is present in a cell [formulas]

Published on Jul 19, 2016 in Excel Howtos, Learn Excel
Find out how many times a value is present in a cell [formulas]

Here is an interesting problem to start your day.

Let’s say you work as DNA sequencing engineer at The Enterprise. And you just unlocked the sequence that is responsible for all male problems. The early onset of baldness. The sequence code is AAAA. And you want to find out how many times this sequence is found in a sample of DNA strings, in the range B6:B19. Essentially you want the above.

So how do you write the formula?

Continue »

On / Off conditional formatting with this simple trick

Published on Jul 13, 2016 in Learn Excel
On / Off conditional formatting with this simple trick

Here is a quick & awesome way to make your dashboards sexy. Add an on / off switch to your conditional formats.

Take a look at above demo to understand what I mean.

Continue »

What is the sum of values excluding items on stop list? [home work]

Published on Jun 10, 2016 in Excel Challenges, Learn Excel
What is the sum of values excluding items on stop list? [home work]

Okay, this is an extension of the Neither “A” Nor “B” sum problem we discussed few days back.

Imagine you have a table named mydata with a few columns and a stop list named stop.list as shown above.

How would you calculate,

  • Sum of Hours for all activities excluding those in stop list?
  • Sum of all Regular hours for activities not in stop list?

So go ahead and post your answers in the comments.

Continue »