All articles with 'Microsoft Excel Formulas' Tag

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

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 …

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 26th session of Chandoo.org podcast, let’s learn all about Excel !@#$%^+/*(}][<.

I am talking about Excel operators, you silly.

What is in this session?

Do you know Excel has more than 25 operators? That is right. There are a variety of operators beyond the simple + – * and /.

In this podcast, let’s understand all about these operators and how to use them. You will learn,

• Why there is a gap between last & this podcast session
• About Excel operators
• Arithmetic operators
• Text operators
• Reference operators
• Comparison operators
• Closing thoughts

Today, lets talk about an interesting extension to the idea of in-cell charts. Adding average or target markers to the chart.

Adding a marker (like average or target or last year value) can enhance your charts greatly and provide more context. Lets understand how to add marker symbols to in-cell charts.

Recently my iPhone 4 crashed. It is 3.5 years old. And just like any other 3 year old, it started acting weird & crazy one night. The next morning it went silent. It won’t go beyond the Apple logo whenever I start it. Since I couldn’t wait for the phone to start, I took out the SIM card (the phone is unlocked, if you are wondering) and placed it in my old Nokia phone. But alas, none of my contacts are on the SIM. They are in “cloud”.

After a day of answering phone calls from everyone including my mom as “Chandoo here”, I’ve decided to get my contacts back. So I logged in to iCloud to download a backup. And the backup was a .VCF file.

Since I wanted to have all my contact numbers in a spreadsheet, I did what any Excel nerd would do. I built a template that can convert VCF data to Excel worksheet.

Imagine you run a cute little pastry in Rome (Italy). To boost the sales you have a 2 person sales team. Caterina & Antonio. Caterina is the manager & Antonio, her assistant. Apart from basic salary, they will also receive sales commission. This comes from a portion of net profit allocated to “incentive pool”. The […]

Recently, we discussed about the case of unwieldy data and how we lookup what we want using formulas like SUMIFS. Today, let us learn few more ways to solve the same problem.

Suitable structure spawns simple solutions

Poorly structured is the 2nd biggest problem of analysts. The first one is not enough coffee. That is why there is a dictum in the data analytics world.

Structure is everything

So, we can easily solve our lookup problem, if our data were to magically re-arranged in 2 column fashion – Data & Value.

Occasionally we deal with data that is so uncooperative that we might as well give up and go back to calculators & ledger books.

Recently I found myself in such a situation and learned something new.

Introducing … data that won’t play nice

Drum roll please. Here is a data-set that I got from somewhere.

The problem – build a lookup formula

And the problem. Oh, simple. Write a lookup formula to find how many customer walk-ins we have on any given day.

But how?

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.

Recently, I saw this chart on Economist website.

It is trying to depict how various cities rank on livability index and how they compare to previous ranking (2014 vs 2009).

As you can see, this chart is not the best way to visualize “Best places to live”.

Few reasons why,

• The segregated views (blue, gray & red) make it hard to look for a specific city or region
• The zig-zag lines look good, but incredibly hard to understand % changes (or absolute changes)
• Labels are all over the place, thus making data interpretation hard.
• Some points have no labels (or ambiguous labels) leading to further confusion.

After examining the chart long & hard, I got thinking.

Its no fun criticizing someones work. Creating a better chart from this data, now thats awesome.

Here is a charming little problem to kick start your day.

Lets say you run a cute little bakery around the corner. Since you want your prices to look charming, you have a policy to round them down or up based on below rule.

If the price ends with 0, 1 or 2 cents, round it down to 9 cents.

If the price ends with 3, 4 or 5 cents, round it up to 5 cents.

If the price ends with 6, 7, 8 or 9 cents, round it up to 9 cents.

For example,

So how do you round to nearest charmed price? You could do it manually. But you would rather bake a few more of those Tiny Cup Cakes than waste time rounding the prices. So you want an automatic way to round prices. This is where Excel helps.

ABC analysis is a popular technique to understand and categorize inventories. Imagine you are handling inventory at a plant that manufactures high-end super expensive cars. Each car requires several parts (4,693 to be exact) to assemble. Some of these parts are very costly (say few thousand dollars per part), while others are cheap (50 cents per part). So how do you make sure that your inventory tracking efforts are optimized so that you waste less time on 50 cent parts & spend more time on costly ones?

This is where ABC analysis helps.

We group the parts in to 3 classes.

• Class A: High cost items. Very tight control & tracking.
• Class B: Medium cost items. Tight control & moderate tracking.
• Class C: Low cost items. No or little control & tracking.

Given a list of items (part numbers, unit costs & number of units needed for assembly), how do we automatically figure which class each item belongs to?

And how do we generate above ABC analysis chart from it?

My mom will be very unhappy with this post. She always told me to focus on one thing at a time. But in this post we are talking about 3 things, not one. Sorry mom.

1. Thank you

I want to thank you for visiting chandoo.org & supporting us.

As I am about to leave to USA for attending Excelapalooza conference, I couldn’t help but be amazed at how much you have given me & my family. Almost 4.5 years ago, when I left my plush corporate job to work full time on Chandoo.org, I had no clue how the future will unfold. Today my heart is full of happiness, my family is secure, my site has grown by heaps and our community (especially you) is awesome.

Without your enthusiasm to learn and keen desire to become awesome, I would not have a job (of running this website). You inspire me to learn new things everyday so that I can share them with you.

Thank you for all the visits, clicks, comments, emails, tweets, likes, signups, purchases & love.

Thank you.

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.

Time for another quick Excel tip.

Lets say the park near your house rents tennis courts by hour. And they charge $10 per hour. At the end of an intense tennis playing week, Linda, the tennis court manager called you up and said you need to pay $78 as rent for that week.

How many hours did you play?

Of course 78/10 = 7.8 hours.

But we all know that 7.8 hours makes no sense.

We also know that 7.8 hours is really 7 hours 48 minutes.

So how to convert 7.8 hrs to 7:48 ?