fbpx
Search
Close this search box.

All articles in 'Excel Howtos' Category

What is the average speed of this road trip? [Solution & Discussion]

Published on Aug 18, 2014 in Excel Howtos, Formula Forensics
What is the average speed of this road trip? [Solution & Discussion]

Last week, we had an interesting homework problem – What is the average speed of this road trip?

We received more than 150 answers. But to my surprise, 57 of them are wrong. So today, lets learn how to calculate the average speed correct way.

Continue »

Matching transactions using formulas [Accounting]

Published on Jun 6, 2014 in Excel Howtos, Learn Excel
Matching transactions using formulas [Accounting]

Imagine you are the head of Accounts Receivable department at a large company. Drab, I know, But humor me and imagine.

Now, every month you get a transaction report.

And you want to know which numbers are matching up.

i.e, if your company gave Vendor-0002 $872.34 on 1st of April, 2014 and your received below payments from them subsequently,

  • $427.77 on 1st April
  • $152.88 on 2nd April
  • $291.69 on 2nd April

Then you consider the account matched since the total received is same as total payable.(427.77 + 152.88 + 291.69 = 872.34).

Continue »

Multiplying 24ft 9inches with 6ft 3inches using Excel

Published on Apr 14, 2014 in Excel Howtos
Multiplying 24ft 9inches with 6ft 3inches 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?

Continue »

Calculating Maximum Change [solutions & discussion]

Published on Mar 26, 2014 in Excel Howtos, Formula Forensics
Calculating Maximum Change [solutions & discussion]

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.

Continue »

Dynamic (Cascading) Dropdowns that reset on change

Published on Feb 13, 2014 in Excel Howtos, Posts by Jeff, VBA Macros

Dynamic dropdowns are a handy way to get your users to make choices based on what they’ve previously chosen, while steering them away from making invalid choices. Today we’re going to look at one that easily handles multiple levels, and we’ll take a look at what could go wrong. Let’s see one in action, shall […]

Continue »

How to select a random sample from all your data [trick]

Published on Jan 31, 2014 in Excel Howtos
How to select a random sample from all your data [trick]

The other day, I got a text message (SMS) from one of our readers. It read,

So today, let us learn a very easy trick to select random sample from your data.

Continue »

Big trouble in little spreadsheet

Published on Jan 17, 2014 in Excel Howtos, hacks, Learn Excel, Posts by Jeff

Howdy folks. Jeff here. I recently gave a presentation on Excel efficiency to a bunch of analysts, in which – among other things – I’d pointed out that if you ever find yourself having to switch calculation to Manual, there’s probably something wrong with your spreadsheet. Here’s the slide:   This prompted one of the […]

Continue »

Find first non-blank item in a list with formulas

Published on Jan 15, 2014 in Excel Challenges, Excel Howtos
Find first non-blank item in a list with formulas

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:

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?

Continue »

Quickly combine text in multiple cells using this trick! [Formulas]

Published on Jan 13, 2014 in Excel Howtos
Quickly combine text in multiple cells using this trick! [Formulas]

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.

Continue »

Sporadic Totals in Excel [video]

Published on Dec 18, 2013 in Excel Howtos
Sporadic Totals in Excel [video]

If this Excel problem is a Bollywood (Indian movie) plot, it would go like this:

Situation: Your boss gave you a worksheet. It has a lot of number chunks. And you need to calculate the sum of each chunk. Quickly!

Twist #1: The villain (your boss, who else) has abducted your spouse. For every extra hour you spend on the problem, your boss will make your spouse go thru one of the boring 97 slide strategy presentations. And his laptop is full of those strategy presentations.

Twist #2: The F1 key on your keyboard is missing.

Twist #3: The coffee machine in your floor is broken again.

Twist #4: And just when you are pressing CTRL+S, the movie steers in to an item song.

—-

Fortunately, no one abducted your spouse. And hopefully the coffee machine is working. But the Excel problem remains unsolved.

Continue »

What is the coolest Excel trick you have learned this year?

Published on Dec 13, 2013 in Excel Howtos
What is the coolest Excel trick you have learned this year?

We are almost at the end of 2013. Time to review how much more awesome we became this year. Today let me ask you a very simple question.

What is the coolest Excel trick you have learned this year?

Go ahead and post your answers using comments.

Continue »

How to find what is in the hidden cells? [quick tip]

Published on Dec 11, 2013 in Excel Howtos
How to find what is in the hidden cells? [quick tip]

You have been there. You are looking at a complex workbook with some hidden rows /columns. You want to know what is in that hidden cells. Alas, the worksheet is protected.

It is like the special coffee machine on top floor reserved for senior executives. You know it is there, but you cannot get to it. Now what?!?

Simple. Follow this process.

(I am talking about hidden cells, not the coffee machine.)

Continue »

Formula Forensics-No. 036: Calculating Costs that Vary by Year and Age

Published on Nov 26, 2013 in Excel Howtos, Formula Forensics, Posts by Sajan

In this episode of Formula Forensics, lets talk about how to build a model where costs vary per year and age. In this example, we are talking about a plantation project. The costs & yields of plants vary by the year they are planted in and the age of plants. In this case, how do we calculate the total costs or returns with formulas?

FF36-02

Continue »

Find last day of any month with this simple trick [formulas]

Published on Nov 21, 2013 in Excel Howtos
Find last day of any month with this simple trick [formulas]

Here is a handy trick to calculate last day of any month.

Assuming y and m contain the year & month for which you want to find the last day’s date, write

=DATE(y, m+1,0)

That is right, you can use ZERO (0) as the day.

When you do this, Excel tells us the last day of previous month.

Continue »

Replace formulas with values using this shortcut [quick tip]

Published on Nov 11, 2013 in Excel Howtos
Replace formulas with values using this shortcut [quick tip]

Often in my work, I need to replace a bunch of formulas with values. Blame it on old habits, but this is what I used to do:

  1. Copy the cells with formulas (CTRL+C)
  2. Press ALT+ESV and then enter.

While this is ok, it does take quite a bit of time and key strokes.

Here is a shorter way I recently learned…

Continue »