All articles with 'array formulas' Tag

VLOOKUP the last value

Published on Aug 11, 2015 in Excel Howtos, Learn Excel
VLOOKUP the last value

VLOOKUP is one of the most useful Excel functions. So much so that I even wrote a book about it. But it has one serious limitation.

It looks up the first occurrence and returns corresponding data.

What if you want to find the last value?

Say, for example, you are looking at a task assignment list and want to know what is the last task assigned to employee Emp13?

We want to extract the task “Make amazing workbook”. Of course our good old VLOOKUP stops once it finds Emp13 and returns the answer as “Create intuitive workbook”.

Continue »

Calculate sum of top 10 values [formulas + homework]

Published on Aug 4, 2015 in Formula Challenges, Learn Excel
Calculate sum of top 10 values [formulas + homework]

Analyzing top n (or bottom m) items is an important part of any data analysis exercise. In this article, we are going to learn Excel formulas to help you with that.

Let’s say you are the lead analyst at a large retail chain in Ohio, USA. You are looking at the latest sales data for all the 300 stores. You want to calculate the total sales of top 10 stores.  Read on to learn the techniques.

Continue »

Who is the most consistent seller? [BYOD]

Published on Feb 18, 2015 in Analytics, Excel Howtos, Pivot Tables & Charts
Who is the most consistent seller? [BYOD]

Who is the most consistent of all?

Imagine you are a category manager at a large e-commerce company. Your site offers various products, but you don’t really make these products. You list products made by other vendors on your site. Every day, these vendors would send you invoices for the amount of product they have sold. Above is a snapshot of such invoices.

Looking at this list, you have a few questions.

  1. Who is the best seller?
  2. Who is the most active seller?
  3. Who is the most consistent seller?
  4. Which seller has fewest invoices?

Let’s go ahead and answer these using Excel. Shall we?

Continue »

Excel to the Next Level by Mastering Multiple Occurrences

Published on Dec 9, 2014 in Learn Excel
Excel to the Next Level by Mastering Multiple Occurrences

This is a guest post by Sohail Anwar.

August 29, 1994. A day that changed my life forever. Football World Cup? Russia and China de-targeting nuclear weapons against each other? Anniversary of the Woodstock festival?

No, much bigger: Two Undertakers show up at WWE Summerslam for an epic battle. Needless to say: MIND() = BLOWN().

And thus begun one boy’s journey into understanding the phenomenon of Multiple Occurrences.

My journey continued, when just a few years later my grandfather handed me down a precious family heirloom: A few columns of meaningless data that I could take away and analyze in Excel. You may laugh but in the 90’s, every boy only wanted two things 1) Lists of pointless data and …

Continue »

The ultimate VLOOKUP trick – Multi-condition Lookup

Published on Oct 28, 2014 in Learn Excel
The ultimate VLOOKUP trick – Multi-condition Lookup

This is a guest post by Sohail Anwar.

Let’s not bore you with an intro. You are about to learn a VLOOKUP trick that Lucifer himself would not want you to know. It’s so absurdly powerful that it was developed in a lab and had to be tested on Rocky’s arch nemesis Ivan Drago.

Presenting the Multiple criteria VLOOKUP!

…boring…pass, we’ve seen it.

Oh, have you? Not like this you haven’t. This will change the way you work with Excel.

Let me start with an easy example. Here’s some data and we would love to know what Bb and Dd is.

Continue »

3D Max Formula for Excel

Published on Sep 9, 2014 in Excel Howtos, Learn Excel
3D Max Formula for Excel

We all know about the MAX formula. But do you know about 3D Max?

Sounds intriguing? Read on.

Lets say you are the sales analyst at ACME Inc. Your job involves drinking copious amounts of coffee, creating awesome reports & helping ACME Inc. beat competition.

For one of the reports, you need to find out the maximum transactions by any customer across months.

But there is a twist in the story.

Your data is not in one sheet. It is in multiple sheets, one per month.

Continue »

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 »

CP016: 3 Must have books for aspiring analysts

Published on Aug 7, 2014 in Chandoo.org Podcast Sessions
CP016: 3 Must have books for aspiring analysts

In the 16th session of Chandoo.org podcast, lets review 3 very useful books for aspiring analysts.

What is in this session?

Analytics is an increasingly popular area now. Every day, scores of fresh graduates are reporting to their first day of work as analysts. But to succeed as an analyst?

By learning & practicing of course.

And books play a vital role in opening new pathways for us. They can alter the way we think, shape our behavior and make us awesome, all in a few page turns.

So in this episode, let me share 3 must have books for (aspiring) analysts.

Continue »

Top 10 things we struggle to do in Excel & awesome remedies for them

Top 10 things we struggle to do in Excel & awesome remedies for them

Recently we asked you, what do you struggle doing in Excel? 170 people responded to this survey and shared their struggles. In this post, lets examine the top 10 struggles according to you and awesome remedies for them.

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 »

Calculate maximum change [homework]

Published on Mar 21, 2014 in Formula Challenges
Calculate maximum change [homework]

Today, lets see how good your formula skills are.

Calculate maximum change

Can you calculate what is the maximum change in product sales between 2 months for above data?

Continue »

Combine pie and xy scatter charts – World Polls chart revisited

Combine pie and xy scatter charts – World Polls chart revisited

Few days ago, we learned how to create a pie+donut combination chart to visualize polls around the world in 2014. It generated quite a bit of interesting discussion (47 comments so far). One of the comments was from Roberto, who along with Kris & Gábor runs The FrankensTeam an online library of advanced Excel tricks, charts and other mind-boggling spreadsheet wizardry.

I really liked Roberto’s comments on the original post and a charting solution he presented. So I asked him if he can do a guest post explaining the technique to our audience. He obliged and here we go.

Over to FrankensTeam.

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 »

Free 2014 Calendar, daily planner templates [download]

Published on Jan 2, 2014 in Learn Excel
Free 2014 Calendar, daily planner templates [download]

Here is a New year gift to all our readers – free 2014 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.
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 »