Archive for January, 2015

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?

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.

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

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.

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.

A little late to the party, but nevertheless right on track.

As Chandoo.org starts another year of making you awesome, I want to take a few minutes of your time to understand how I can help you better this year?

Please answer this 1 question survey for me.
If you do not see the form below, please click here

Here is a common problem. Imagine you are looking a complex spreadsheet, aptly titled “Corporate Strategy 2020.xlsx” which as 17 tabs, umpteen formulas and unclean structure. Whoever designed it was in insane hurry. The workbook has formulas like this, =SUM(Budget!A2:A30, 3600)+7925 .

It was as if Homer Simpson created it while Peter Griffin oversaw the project.

So how do you go about detecting all cells containing formulas with hard-coded values?

This is a guest post by Sohail Anwar. 

Why do so many of us use Excel? Let’s trace it back to the ’80s when Microsoft hit gold by being the first out of the blocks with the widely available operating system that was somewhat dummy proof.

Suddenly everyone could aspire to launch ‘Nukes’ like a fresh faced Matthew Broderick in the film ‘War Games’. 

By the early 90’s Windows had become even more established relative to other Operating Systems, so much so that PC manufacturers were developing components around Windows’ capabilities and suddenly PCs were Windows machines. As big business began accepting the significance of computing, Microsoft started winning huge licensing contracts with all the major corporations in all sectors, but the Finance sector in particular, where Excel would be king, was having an exponential boom at this time. For big organisations, once you spent a fortune buying licences for the Operating System it only made sense to purchase the seamlessly integrated and carefully developed/tested apps to run on them; enter Excel, Word, PowerPoint and eventually Outlook. Fast forward to 2015 and we are firmly in the age of second generation corporate professionals who have developed much of their productivity skill sets around those particular Windows tools. While all the excellent tools have their place, Excel stands out and here are 8 reasons why you need to up your Excel game more than ever this year.

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 27th session of Chandoo.org podcast, let’s pave way for an awesome 2015.

We are going to talk about 15 proven strategies for making you awesome in Excel & Your work.

What is in this session?

We all get fresh dose of energy, enthusiasm & drive during new years. So we aim for bigger & more awesome things. But once the first few weeks are over, we just settle down to the normal rhythm and forget about these big, hairy & audacious goals.

Let’s make 2015 different. In this podcast, Let’s understand how you can become awesome in Excel & your work this year, with 15 proven strategies:

• Announcements – my new year & plans for next few months
• Becoming awesome – 3 important areas of focus
• Learning
  • New formulas
  • New features
  • Different charts
  • Macros
  • Linkup Excel with other software
  • Get a book
  • Join a course
• Application
  • Take up a work project
  • Consulting
  • Mimic a chart in Excel
  • Beyond XL – Power Pivot etc.
• Sharing
  • Forums
  • Helping a colleague
  • Comment on blogs
  • Train your team

Want to write formulas faster? Here is a quick tip.

Use Auto-correct!

That is right. Excel’s auto-correct feature can be setup to help you write formulas faster. See above demo. Read on for details.

Here is a New year gift to all our readers – free 2015 Excel Calendar & daily planner Template.

This calender has,

• One page full calendar with notes, in 4 different color schemes
• Daily event planner & tracker
• 1 Mini calendar
• Monthly calendar (prints to 12 pages)
• Works for any year, just change year in Full tab.