Archive for September, 2016

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 »

Mapping Spread of Obesity [Chart Critique and Alternatives]

Mapping Spread of Obesity [Chart Critique and Alternatives]

Over at Flowing Data, Nathan has published an interesting visualization about spread of obesity.

While the above chart is quite interesting, it doesn’t offer much insight in to the data. There are a few drawbacks,

  • Understanding obesity trends for a given state over the years is hard due to the layout and format of the chart.
  • Finding which states are experiencing most obesity growth rates is not possible
  • Ironically, the chart itself is obese. It takes too much space to explain the data.

Nevertheless, the chart looks cool and can be reused with smaller data-sets (quarterly trends or just for a few years). So let’s recreate the same in Excel. While we are at it, let’s also build an alternative visualization to explore the obesity data.

Continue »

Visualizing Financial Metrics – Contest Winners

Published on Sep 23, 2016 in Cool Infographics & Data Visualizations
Visualizing Financial Metrics – Contest Winners

Its party time! A while ago, we ran a contest asking you to visualize financial metrics data. We got 30 awesome entries. Today let’s reward the winners.

But first a quick note: Congratulations to all the entrants. I have learned a lot just by playing with your files and exploring them. Thank you so much for taking time to share your knowledge with all of us.

Continue »

Visualizing target vs. actual progress – Biker on a hill chart

Visualizing target vs. actual progress – Biker on a hill chart

Over the years, we have discussed a whole heap of techniques to visualize budget vs. actual charts. Today let’s take a ride on this slope again and learn another fun, silly & awesome way to depict target vs. actual progress.

Introducing biker on a hill chart

Tada!!!

Biker on a hill!?! Don’t worry, I didn’t fall down on a descent and lose my brain. I am talking about an Excel chart to visualize target vs. actual progress on a time line with biker on a hill analogy. See the above chart, you will know.

Looks interesting? Read on to learn how to create this in Excel.

Continue »

Stacked Bar/Column Chart with Indicator Arrows – Advanced

Published on Sep 15, 2016 in Charts and Graphs, Excel Howtos, Huis, Posts by Hui
Stacked Bar/Column Chart with Indicator Arrows – Advanced

Lets take last weeks Stacked Bar/Column Chart and add some high-performance steroids.

Continue »

Formula Forensics No. 041 – Convert a Roman Numeral to a Number

Published on Sep 14, 2016 in Excel Howtos, Formula Forensics, Huis, Posts by Hui
Formula Forensics No. 041 – Convert a Roman Numeral to a Number

Learn how to convert a Roman Numeral to a Number using this nifty formula. No VBA required.

Continue »

Sara’s Copy Shop – Break even analysis and what-if modeling in Excel [Videos]

Published on Sep 13, 2016 in Analytics, Financial Modeling
Sara’s Copy Shop – Break even analysis and what-if modeling in Excel [Videos]

Last week, I asked you to share an analysis problem that you couldn’t solve in Excel. We got quite a few very interesting problems in comments and email. In this post, let me explain how to solve Sara’s copy shop problem using Excel.

What is Sara’s copy shop problem?
Thanks to Caroline who posted this problem.

Sara wants to open a copy shop. Each copier costs $5,000 per year to lease. The rent & other fixed costs per month are $300. There is a $0.02 variable cost per copy. Each copier can print up to 100,000 copies per year. She plans to charge $0.11 per copy from her customers. Sara estimates that the demand can be any of the 4 values – 500, 1000, 1500 or 2000 copies per day.

  1. Build a model to estimate profit per given number of copiers & demand values
  2. Find the mix of copiers & demand values that can make maximum profit for Sara (copiers – 1 to 6, demand – 500 to 2000)
Continue »

Stacked Bar and Indicator Arrow Chart – Tutorial

Published on Sep 12, 2016 in Charts and Graphs, Excel Howtos, Huis, Posts by Hui
Stacked Bar and Indicator Arrow Chart – Tutorial

Learn how to develop a Stacked Bar chart with Indicator Arrow in this Tutorial

Continue »

Tell me about an analysis problem that you couldn’t solve with Excel?

Published on Sep 9, 2016 in Analytics

Time for a quick show & tell.

Tell me about an analysis problem that you couldn’t solve with Excel?

It can be because you didn’t know how to solve the problem or Excel isn’t the tool for it or any other reason.

Go ahead and speak up. Post your tricky analysis problems in the comments section.

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 »

Hourly Goals Chart with Conditional Formatting

Published on Sep 1, 2016 in Charts and Graphs, Excel Howtos, Huis, Posts by Hui
Hourly Goals Chart with Conditional Formatting

A while back I developed a solution to a Chandoo.org Forum question, where the user wanted a 4 level doughnut chart where each doughnut was made up of 12 segments and each segment was to be colored based on a value within a range. If the values changed he wanted the chart to update, Conditional Formating like:
This post looks at how this was achieved.

Continue »