Chandoo.org - Learn Excel, Power BI, Charting, Dashboards and VBA online

Latest Tips & Tutorials

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.

September 14, 2016

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.

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

Read This »

September 13, 2016

Stacked Bar and Indicator Arrow Chart – Tutorial

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

Read This »

September 12, 2016

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

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.

Read This »

September 9, 2016

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.

Read This »

September 5, 2016

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.

Read This »

September 1, 2016

Visualizing Financial Metrics – 30 Alternatives

Around 2 months back, I asked you to visualize multiple variable data for 4 companies using Excel. 30 of you responded to the challenge with several interesting and awesome charts, dashboards and reports to visualize the financial metric data. Today, let’s take a look at the contest entries and learn from them.

First a quick note:

I am really sorry for the delay in compiling the results for this contest. Originally I planned to announce them during last week of July. But my move to New Zealand disrupted the workflow. I know the contestants have poured in a lot of time & effort in creating these fabulous workbook and it is unfair on my part. I am sorry and I will manage future contests better.

Read This »

August 30, 2016

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.

Read This »

August 26, 2016