All articles with 'array formulas' Tag
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.
- Who is the best seller?
- Who is the most active seller?
- Who is the most consistent seller?
- Which seller has fewest invoices?
Let’s go ahead and answer these using Excel. Shall we?Continue »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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?Continue »
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.
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?
Yesterday was Halloween. To our readers who are not familiar with it, ‘Halloween is a colorful festival with lots of costumes, scary stories, theme parties and trick-or-treating, celebrated on 31st October, every year.” I have never celebrated Halloween as it is an unknown tradition in India where I live. But that is no excuse. Especially when the celebration calls for colorful clothes, scary themed houses and shrieking kids.
Of course, we are not going to have a traditional Halloween. Because,
- At our house, we use pumpkins to make sambar, not lights
- The only ones with costumes in our house are my kids.
- If I send my kids for trick or treat, they will get neither.
So that brings us to the only part of Halloween that I can celebrate. Telling scary stories.
So lets talk about the stuff that scares us. But bear in mind that I am not interested in that time when you & your cousin went camping and stumbled in to an abandoned log cabin to discover the …Save it for real Halloween.
We want to talk stuff that scares you in Excel of course.Continue »
Today lets get cozy. Lets start a fling (a very long one). Lets do something that will make you smart, happy and relaxed.
Don’t get any naughty ideas. I am talking about INDEX() formula.
Of all the hundreds of formulas & thousands of features in Excel, INDEX() would rank somewhere in the top 5 for me. It is a versatile, powerful, simple & smart formula. Although it looks plain, it can make huge changes to the way you analyze data, calculate numbers and present them. It is so important that, whenever I teach (live or online), I usually dedicate 25% of teaching time to INDEX().
Enough build up. Lets get cozy with INDEX.Continue »