Archive for October, 2011

Using an Array Formula to Find and Count the Maximum Text Occurrences in a Range

Using an Array Formula to Find and Count the Maximum Text Occurrences in a Range

Chandoo is off on Holidays and once again thrown me the keys to Chandoo.org.

So this week we’re going to:

  • Pull apart an Array Formula and see what’s under the hood;
  • Have a look at a technique for quickly developing Custom Number Formats &
  • Look at how we can use HTML Codes in posts at Chandoo.org to spice up your questions and answers.

Today is Tarun’s Problem

“I have got multiple names in each row and would like to have what name is repeated maximum number of times and how many times?”

Today we pull apart an answer to this question and see how it works.

Continue »

Compare 2 Lists Visually and Highlight Matches

Published on Oct 27, 2011 in Excel Howtos, Learn Excel
Compare 2 Lists Visually and Highlight Matches

Comparison is one of the most common things we do with Excel. Naturally, there are so many ways to compare 2 lists of data using Excel.

Today, I want to share an interesting comparison problem with you.

Lets say you run a small shop which sells some highly specialized products. Now, since your products require quite some training before customers can buy them, you keep track of all product queries and arrange demos.

After a hectic week, you are staring at 2 lists. One with product queries, another with product demos. And you want to know whether all the queries are answered with a demo or not.

Continue »

Tell us what you would like to learn in our Excel for PMs course [Survey]

Published on Oct 25, 2011 in products, Project Management
Tell us what you would like to learn in our Excel for PMs course [Survey]

Few days ago, we announced about our upcoming Excel for PMs course. Quite a few of emailed me and told that you are looking forward for this.

Today I want to share our preliminary agenda for this course and take your help in fine-tuning it.

Continue »

Excel Links – After a long time edition

Published on Oct 24, 2011 in excel links

Long time readers of Chandoo.org know that we have a regular featured called as “Excel Links“, where I share some interesting Excel articles published around the web. This used to be a weekly feature, then became a monthly thing and later once a few months thing. As I had a hectic weekend, when I got […]

Continue »

Thank you, We have a home [personal story]

Published on Oct 20, 2011 in personal
Thank you, We have a home [personal story]

I have a happy news to share. Recently, we moved in to our new home.

As we live, breathe, enjoy and sleep in this home, I could not thank enough for your support in making this happen. I am so glad you identify with the mission of Chandoo.org and spread a good word about it. I am so humbled that you support us by joining our training programs, purchasing our e-books & products. I am so thankful to you for spending time with us everyday, learning & sharing new things.

Thank you.

I am also thankful to MS Excel, the powerful, awesome tool that transformed my life. It gave me the courage to quit a comfortable job and embark on an exciting journey of running a small business. Thank you so much Microsoft & Excel for being awesome.

Read this to find out the back story and see the pics of our home.

Continue »

Split an Excel File in to Many using VBA [Videos]

Published on Oct 19, 2011 in VBA Macros
Split an Excel File in to Many using VBA [Videos]

Splitting an Excel file in to many is easier than splitting bill in a restaurant among friends. All you need is advanced filters, a few lines of VBA code and some data. You can go splitting in no time.

Context:

Lets say you have lots of data like this in a file. And you want to split this in to multiple files, one per salesperson.

Solution – Split Data in to Multiple Files using Advanced Filters & VBA

The process of splitting data can be broken down to 4 steps …,

Continue »

Quickly Fill Blank Cells in a Table [Reader Tip]

Published on Oct 17, 2011 in Excel Howtos
Quickly Fill Blank Cells in a Table [Reader Tip]

This post is authored by Martin, one of our readers.

Situation:

Sometimes I encounter data in my tables with blank cells where there is a repeated value from the cell directly above. See below:

This can be annoying when it comes to interpreting the data and when sorting columns.

Solution:

To know how I solve this, just read rest of the article.

Continue »

Commercial Aspects of Project Management

Published on Oct 14, 2011 in Project Management
Commercial Aspects of Project Management

In 2005, fresh out of MBA School, I was put on managing an e-business transformation strategy project. It was then that realized that project management was a full time job (I always wondered before that why PMs were even paid a dime!). We created huge Project Management reports for the client, where were tracked and […]

Continue »

Filter values where Fruit=Banana OR Sales>70. In Other Words, How to use Advanced Filters?

Published on Oct 10, 2011 in Excel Howtos, Learn Excel
Filter values where Fruit=Banana OR Sales>70. In Other Words, How to use Advanced Filters?

Filtering is a simple yet powerful way to analyze data. When you apply filters to any list of values, Excel gives you some really useful pre-defined filters so that you can analyze the data in a variety of ways. So, assuming you have data like this: We all know how to filter data for Bananas. […]

Continue »

Offset() function to Calculate IRR for Dynamic Range

Published on Oct 4, 2011 in Financial Modeling

Offset() function to Calculate IRR for Dynamic Range When you start the project can you be sure, for how long will you operate it? A VC gives you funds to buy a commercial project. You are to operate the project for some time and then sell it off! Can you tell me today, when you […]

Continue »

A Challenge from Hui

Published on Oct 3, 2011 in Excel Challenges, Posts by Hui

Hi All. I am about to head off on holidays for a couple of weeks during which time I will only have limited internet access and won’t be helping out here at Chandoo.org or at the ExcelHero Academy whilst away. I thought I may leave a few words and a Challenge for everyone. History In […]

Continue »