Archive for October, 2016

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 56th episode of Chandoo.org podcast, let me answer the chicken and egg question of Excel users. How many formulas should you care to learn?

What is in this session?
In this podcast,

• Two personal updates
• 3 legs of formula writing
  • Function knowledge
  • Operators
  • Referencing
• 6 categories of must-know functions
  • Basic math
  • Conditions
  • Lookups
  • Text
  • Date & time
  • Work specific
• Closing remarks & resources for you

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 at twitter, @for_the_moves asks,

@For_the_moves Same as growing your vocabulary. Remember, words (or functions) = ideas. the more you know, the better you can think.

— Chandoo.org (@r1c1) October 12, 2016

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

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.

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

Ladies & gentlemen, its time we revived the much loved Chandoo.org podcast. In the 55th episode, I do a lousy imitation of Arnold Schwarzenegger’s famous “I will be back” and tell you why there was such a long gap between episodes, my plans for reviving our podcast and more.

What is in this session?

In this podcast,

• Why there was such a long gap between last and this episode
• What next?
• How to extract every 6th item from a list?

It is election time in USA, and that means there is a whole lot of drama, discussions and of course data analysis. There are tons of cool visualizations published on all the data. Previously, we talked about “How Trump happened” chart.

Today let’s take a look at the beautiful decision tree chart by NY Times explaining what would happen if each of the 10 swing states vote for Democrats or Republicans. Go ahead and look at that chart. And when you are done playing with it, come back.

My first thought after looking at the chart is: Wow, that is cool. I wonder how we can recreate that experience in Excel?

But as you can guess, making a dynamic tree visualization in Excel is pretty hard. You can create a bubble chart mixed with XY chart to show all the nodes of the decision tree, but as this tree has 2^10 nodes at the bottom level (and 2^11-1 total nodes) our chart would look very clumsy and busy.

So, instead of replicating NY Times chart, why not make our own version that explains the data? You can reuse this idea when visualizing outcomes of several what-if scenarios.

Anyone who has made a pivot table and their grandma knows that formatting them is a pain. Let’s recap the steps to apply one of the most common formats – currency format.

1. Right click on any value field
2. Go to Value field settings
3. Click on “Number Format” button
4. Choose Currency format
5. Close the boxes, one after another

Unless you get paid per click, you wont be happy with all those clicks.

Wouldn’t it be cool to just click once and apply most common format to your pivot fields?

Of course you can. Just add oneClickCurrency macro to your personal macros workbook. And then add this to your Home ribbon as a custom button and you have a one click format option for any pivot.

Time for a quick but very useful tip. Ever wanted to create all combinations from two (or more) lists? a la Cartesian product of both lists.

Here is a ridiculously simple way to do it.

Wellington(NZ) sure is beautiful on a clear day. There are so many cool bike tracks, walking trails and beaches to keep you busy. Today, I went cycling on my usual route towards Makara beach. At the turn off point, I thought, “This is selfish. I can’t keep all this beauty to myself. I must share it with you.” So here we go.

A quick video about data analysis while on bike

Technically, I was not on bike when recording this video.  Watch it below or on our YouTube Channel.