All articles with 'Microsoft Excel Formulas' Tag

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 9th session of Chandoo.org podcast, lets raise above AVERAGEs.

AVERAGEs are a very popular and universal way to summarize data. But do you know they are mean? Mean as in, AVERAGEs do not reveal much about your data or business. In episode 9 of Chandoo.org podcast, we tackle this problem and present solutions.

In this podcast, you will learn,

• What is AVERAGE?
• Pitfalls of averages
• 5 statistic concepts you must understand
  • Standard Deviation
  • Median
  • Quartiles
  • Outliers
  • Distribution of data
• What next?

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

Here is a problem we all face once in a while. We inherit this bulky, bloated, leaking at the edges workbook from a colleague. Now the onus of maintaining it is on us. The person who made this workbook is nowhere to be found. May be she is vacationing in Hawaii sipping pineapple juice. May be he became a vice president and roaming the country in your company’s private jet.

So what do we do? How do we handle this inheritance?

That is the topic of our podcast, episode 8.

In this podcast, you will learn,

• An overview of the inheritance problem
• 6 Tips to understand workbooks made by someone else
• Tip 0: Talk to the creator
• Tip 1: Model the workbook on paper
• Tip 2: Locate the engine, ie the formulas
• Tip 3: See what else is under the hood – hidden sheets, names, VBA code
• Tip 4: Annotate (add comments) as you learn
• Tip 5: Locate the controls – inputs, assumptions, scenarios
• Tip 6: Re-construct from scratch
• Deep dive in to understanding the formulas
• Deep dive in to understanding VBA code
• Conclusions

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 7th session of Chandoo.org podcast, lets make you aweSUM().

Imagine for a second that Excel cannot add up numbers. And no it cant subtract them either. What would that look like?

A glorified Notepad. That’s right. Excel’s ability to add up numbers, along with features like formulas, charts, pivot tables & BHATTEXT() are what make it such a lovely software. May be not the BHATTEXT(), but we all agree that Excel is so versatile and useful because it can add up numbers (and perform other calculations) with ease.

But how well do you know the SUM formulas of Excel?

In this podcast, you will learn,

• Special personal fruit announcement 😛
• + operator
• Status bar & total rows in tables
• Auto Sum feature
• SUM() function
• SUMIFS function
• Special cases of SUMIFS function
• SUBTOTAL & AGGREGATE functions
• Other summing functions – SUMPRODUCT etc.

Lets talk about how we can use Excel to calculate Compounded Annual Growth Rate (CAGR for short).

What is CAGR? What does it signify?

Let us say you are the CEO of ACME Inc. You have been selling various widgets since 2009. In your latest annual report you want to tell your shareholder at what rate you have been growing ACME Inc. sales. The figure are,

• 2009 – $150 Mn
• 2010 – $125 Mn
• 2011 – $160 Mn
• 2012 – $174 Mn
• 2013 – $195 Mn
• 2014 – $210 Mn

Now, if you see the growth rates, they are all over the place. Right from -16.67% to 28%. But you want to report a single annual growth rate.

This is where CAGR (Compounded Annual Growth Rate) comes handy.

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 6th session of Chandoo.org podcast, we focus on making you a better analyst and propose a road map for getting better at data analysis & improving your career prospects.

In this podcast you will learn,

• Why become a better analyst?
• The road map for becoming a better analyst – BETTER framework
• B for Business Knowledge
• E for Examining user needs
• T for Thinking about analysis
• T for Tools of Trade ie Excel
• E for Expression
• R for Refining yourself
• Conclusions

Last week we learned how to answer questions like, “How many tiles in a room?” using Excel. We learned about CONVERT function and fraction number format settings in Excel.

But why stop at calculation? We can even model a room full of tiles, thanks to Excel’s grid nature.

So today, we will learn how to create a room layout as shown above, using Excel.

Imagine you are a carpenter and you are tasked with laying wooden floor at Gill Bates’ house. Now Gill B has a very big house and he wants to make sure you do a good job. So instead of asking you to lay the floor for entire house, he asks you to finish flooring in the guest bedroom first. Here are the dimensions of that guest bedroom.

• Width: 6ft 3inches
• Length: 24ft 9inches
• Size of individual wooden floor board: 2ft x 4inches

And here is the big question you are facing.

What?!? the guest bedroom width is only 6ft 3inches?

But over the years of chiseling and polishing you have learned to keep quiet and do your work.

So the real question you have is, How many wooden floor boards should you buy?

Of course, you want to find the answer using Excel. Why else would a carpenter read this blog?

Last Friday, we had a fun little Excel challenge – Calculate Maximum Change. More than 170 people commented and shared their solutions to this problem.

And the best part?

The best part is the variety of solutions & thinking displayed by our community. So if you are one of those 170, puff your chest & pat yourself on the back. Go ahead, I will wait.

Today, lets take a look at some of these awesome formulas and understand how they work. Read on and watch the video you below to gain few awesomeness pounds.

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the second session of Chandoo.org Podcast, We will be learning how to use 5 Excel lookup functions.

What is in this session?
In this session, we tackle one of the most important areas of Excel. The lookup functions.

You will learn,

• Why lookup functions are necessary
• 5 Important lookup functions in Excel – VLOOKUP, HLOOKUP, LOOKUP, MATCH & INDEX
• When & how to use each of these 5 functions?
• Extreme scenarios:
  • What happens when the value you are looking up is not there?
  • What if too many items match the lookup value?
  • What if you have too many conditions in the lookup criteria?
• Using IFERROR function
• Re-cap of the new powers you acquired
• 4 Resources for you to learn lookup functions better

One of the most useful features of Excel is formula help box. You know the little yellow box that appears as soon as you start typing a formula in a cell. I use this all the time to understand what the syntax of a particular function is, what parameters to pass etc.

Although I love it, sometimes it does get in the way when writing formulas. Because the help box sits on top of my data, often I find it hard to know which cell to link to.

Solution?!?

Simple. Use your mouse to move away the help box wherever you want.

If you’re building large models, then you may want to use volatile functions – including OFFSET(), INDIRECT(), and TODAY() – with caution, because unless you know what you are doing, they *might* slow Excel down to the point that data entry is sluggish, if not downright tedious.

In fact, you *might* want to consider getting out of the habit of using these functions at all if there are alternatives, and you might want to replace volatile functions in your existing models with non-volatile alternatives…I have reduced recalculation time in large models from minutes to milliseconds by doing just that!

So what the heck does volatile actually mean? And why should you care? Let’s find out, shall we?

Today lets rescue John Doe from John_doe@email.com.

Extract first & last name from email address

Given an email address in the format

firstnameany_non-alphabet.characterslastname@email.com

You need to extract first name & last name using formulas.

Gantt charts are a very popular way to visually depict project plans. Today, let us learn how to use Excel to make quick & easy Project Plan Gantt Chart.

This is what we will be creating.

Blank cells are an invisible pain in the analysis. Dealing with them is frustrating, time consuming and often very complex. At chandoo.org, we are not big fans of blank cells. That is why we wrote:

• How to delete blank cells & rows?
• Dealing with blanks – case study
• Quickly filling blank cells in a table
• Extracting non-blank data from a list

Today, lets talk about one more scenario. Lets say you want to find out the first non-blank item in a list. How would you do it?

Ever wondered how to go from a bunch of cells with text to one big combined text? Like shown above.

Well, there is a simple trick. One of our readers, Grant shared this with us. Read on to learn this.