Did you know you can use Excel 95-03 Color Names or Color Numbers in your Custom Number Formats in all Excel versions up to an including Excel 2013?
This post will show you how.Continue »
Just a quick announcement.
As you may know, I will be speaking at PASS Business Analytics conference in Santa Clara this April (between 20th & 22nd). As a run up to the event, the good folks at PASS are conducting free webinars on various topics of Excel.
Here are 2 topics on which I am doing webinars. You can watch them and pick up some really cool Excel skills. Just click on below links to enjoy them.
- 5 Dashboard mistakes to avoid (watch it any time)
- Excel charting – productivity tips (live on February 3rd)
That is all for now. See you in the webinar.Continue »
Here is a fun problem to think about.
Let’s say you are looking at some data like above.
And you want to find out what is the longest streak of wins in the list.
How do you calculate it?
bonus question: What formula calculates when the longest streak began?Continue »
Imagine you are the in-charge of finance department at Hogwarts. So one fine day, while you are practicing the spells, Dumbledore walks in to your office and says, “Our electricity bills are way too high. As the muggles don’t accept wizard money, we have to find a way to reduce our power consumption.”
So you summoned the previous 12 month utility bills to examine energy consumption patterns, and pretty soon you realized that most of the electricity consumption is due to the light bulbs. You suddenly have a brilliant idea. Why not replace the light bulbs with a variety that consumes low power? A light bulb moment indeed.
Your next step is to figure out what varieties of light bulbs are out there. Fortunately this is easier than catching a snitch in a game of quidditch. A quick search revealed that there are 3 types of light bulbs:
- Regular incandescent bulbs (the kind Hogwarts currently uses)
- Compact Fluorescent Light bulbs (CFL)
- Light Emitting Diode bulbs (LED)
Now your job is to do a cost benefit analysis of these options and pick one.Continue »
In the 28th session of Chandoo.org podcast, let’s figure out how to express business rules & logic to Excel.
What is in this session?
What good are spreadsheets if they can’t solve business problems?
But we all struggle when it comes to modeling real world business conditions in Excel. For example, if you have below business rule to decide how much discount to offer a customer,
- If the customer bought 3 or more times previously and offer 15% discount
- If the customer bought 1 or 2 times previously AND customer’s age is >40, offer 10% discount
- If the customer visited our New York store between 6PM-9PM offer 5% discount
- Else no discount
How would you go about modeling these in Excel?
That is our topic for this podcast session.
In this podcast, you will learn
- The challenge of modeling business logic & rules in Excel
- My struggles with such formulas in early days
- 4 features of Excel that can help you with this.
- Example business rules & how to write formulas
If you are an Excel enthusiast, here is a feast for you.
Just a small glitch, it is a not feast you enjoy with mouth, it is a feast you consume with eyes, ears & mind.
It gives me immense pleasure to invite you to this feast.Continue »
When you are a “work from home” dad, you can see a lot of patterns. Here is one.
My kids come home from school by noon (they are too young for full day school). Right after lunch they watch their favorite cartoon program, Team Umizoomi, in which few fictional characters go about solving problems in the Umi city using maths. Milli, one of the characters is an expert with patterns. She solves problems by identifying patterns and unleashing pattern power.
Team Umizoomi & Excel Fill – How do they link up?
Here is how they link up.
Imagine you have a workbook where you need to follow a pattern, like above.
You too can unleash the pattern power. What more… you needn’t break in to a song sequence every-time you unleash the power.Continue »