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 n 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.

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.

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.

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.

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

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

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)