All articles with 'small' Tag

Rescue oddly shaped data – Battle between Formulas, VBA and Power Query

Published on Apr 11, 2018 in Learn Excel, Power Query, VBA Macros
Rescue oddly shaped data – Battle between Formulas, VBA and Power Query

Let’s say you have data like this in a spreadsheet. Don’t roll your eyes, I am 102% sure, right at this moment, someone is (ab)using Excel to create similar messy data.

How do you reshape it to one column?

You could use formulas, VBA or Power Query. Let’s examine all these methods to see what is best. All these methods assume your data is in a range aptly named myrange.

Continue »

How many ‘Friday the 13th’s are in this year? [Formula fun + challenge]

Published on May 13, 2016 in Excel Howtos, Learn Excel
How many ‘Friday the 13th’s are in this year? [Formula fun + challenge]

Today is Friday the 13th. If you are a raging friggatriskaidekaphobiac, I suggest you to stop reading this post. For the rest of you, I have something fun.

Given a year in cell C3, let’s find out all the months with Friday the 13th. Something like above.

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 »

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 »

Top 10 Formulas for Aspiring Analysts

Published on Jan 16, 2013 in Learn Excel
Top 10 Formulas for Aspiring Analysts

Few weeks ago, someone asked me “What are the top 10 formulas?” That got me thinking.

While each of us have our own list of favorite, most frequently used formulas, there is no standard list of top 10 formulas for everyone. So, today let me attempt that.

If you want to become a data or business analyst then you must develop good understanding of Excel formulas & become fluent in them.

A good analyst should be familiar with below 10 formulas to begin with.

Continue »

Formula Forensics No. 032 – Creating Dynamic Charts with Non-Contiguous Data

Published on Dec 4, 2012 in Formula Forensics, Posts by Sajan
Formula Forensics No. 032 – Creating Dynamic Charts with Non-Contiguous Data

There are many web pages devoted to Excel Charts and there are many web pages devoted to Creating Dynamic Ranges, but a lot of these suffer when the data is poorly organised or non-contiguous.

Today we look at a Technique for Creating Dynamic Charts with Non-Contiguous Data.

Continue »

Formula Forensics No. 030 – Extracting a Sorted, Unique List, Grouped by Frequency of Occurrence

Published on Oct 4, 2012 in Formula Forensics, Posts by Sajan
Formula Forensics No. 030 – Extracting a Sorted, Unique List, Grouped by Frequency of Occurrence

Today at Formula Forensics, Guest author Sajan shows us how to extract a sorted, unique list of items, displaying the most frequently occurring items first, while restricting the output based on some additional criteria.

Continue »

Formula Forensics 018. Retrieving the Nth number from a Range which has Gaps.

Published on Apr 12, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensics 018. Retrieving the Nth number from a Range which has Gaps.

Over Easter, while we were all busy eating our Easter Eggs, at the Chandoo.org/Forums, Slk213 was worried about how to retrieve the Nth number from a row of data which had gaps in it.

Today in Formula Forensics we will look at how to solve this problem.

Continue »

Formula Forensic 014 – Faseeh’s Formula

Published on Mar 1, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensic 014 – Faseeh’s Formula

“I have a large list which includes blanks, I want to retrieve the list without the blanks”
Faseeh, responded with a neat array formula.

Today Formula Forensics pulls Faseeh’s Formula apart to see what makes it tick.

Continue »

Learn Statistics & Probability using MS Excel

Published on Feb 13, 2012 in excel apps, Excel Howtos, Learn Excel, simulation, VBA Macros
Learn Statistics & Probability using MS Excel

One of the most dreaded courses during my under-graduation is Probability, Statistics & Queuing Theory. We called it PSQT. I struggled to understand the significance and concept of this course as I could barely concentrate in the class. We had a professor, who is probably a genius, but the moment he started the class, I would magically fall in to one of my after-noon naps. When I woke up, we are either in the middle of an elaborate t-test or going thru intricacies of a Markovian queue.

This was all 11 years ago. Later in life, I have embraced the world of probability & statistics. I still fear queues. May be I will get there one day. 😉

A good understanding of statistics & probability theory is necessary if you want to model complex real-life problems using Excel or similar tools. Naturally, Excel has several functions, features & supported add-ins to help you in this area.

Today, I want to share some of this with you. This article is broken down in to 3 parts.

  1. Learning Statistics & Probability using Excel
  2. Downloadable Excel Workbooks to understand
  3. Full blown models & simulations in Excel
Continue »

Formula Forensics No. 003 – Lukes Reward

Published on Nov 18, 2011 in Formula Forensics, Learn Excel, Luke, Posts by Luke
Formula Forensics No. 003 – Lukes Reward

Today we publish what is hopefully the First of many posts by guest author Luke M. it is also the Third post in the Formula Forensics series, which has now moved to a regular and new Thursday slot ?

Luke has taken up Hui’s and 3G’s challenge of explaining how the technique he regularly uses on the Chandoo.org Forums for extracting individual records out of a group.

Today Luke tells us how to extract a list of items from a larger list according to some criteria.

Continue »

29 Excel Formula Tips for all Occasions [and proof that PHD readers truly rock]

Published on Aug 24, 2009 in Excel Howtos, Featured, Learn Excel

It is no exaggeration that knowing excel formulas can give you a career boost. From someone starting at the long list of numbers, you can suddenly become a data god who can lookup, manipulate and analyze any spreadsheet.

So when our little excel blog hit the 5000 RSS Subscriber milestone, I celebrated the occasion by asking you to share an excel formula through twitter or comments with rest of us. And boy, what an excellent list of formula tips you have shared with us all.
Here is the complete list of entries for the twitter formula contest.

Continue »

Formula 1 Style Sorting of Times (Durations) in Excel

Published on Jul 15, 2009 in Excel Howtos
Formula 1 Style Sorting of Times (Durations) in Excel

The other day I was watching Formula 1 on TV. I think it is the ideal game to follow for a lazy dude like me. It is on every other weekend. It takes .32 seconds to understand the game and 3.2 seconds to know the points and scoring mechanism. But I am not here to convince you to follow the game. While looking at score boards, it struck me,

“how about writing excel formulas for sorting a list of durations (or numbers) in the formula 1 order?”

Continue »

How to Round and Sort Data using Excel Formulas?

Published on Jul 6, 2009 in Excel Howtos, Learn Excel
How to Round and Sort Data using Excel Formulas?

Cheryl asks via e-mail, “I was wondering if you could help me figure out how to combine the round formula with the rank formula? I need to first round all the numbers and then rank them.”

Of course we can solve this by simply using array formulas. Curious? Find out more by reading the rest of this post.

Continue »

Incell Dot Plots in Microsoft Excel

Published on Jun 10, 2009 in Charts and Graphs, Learn Excel
Incell Dot Plots in Microsoft Excel

Dot plots are a very popular and effective charts. According to wikipedia “Dot plots are one of the simplest plots available, and are suitable for small to moderate sized data sets. They are useful for highlighting clusters and gaps, as well as outliers.”

Today we will learn about creating in-cell dot plots using excel. We will see how we can create a dot plot using 3 data series of some fictitious data.

Continue »