All articles with 'Microsoft Excel Formulas' Tag

Make 1,200 dinosaurs in no time with Excel [formulas]

Published on Jan 29, 2016 in Learn Excel
Make 1,200 dinosaurs in no time with Excel [formulas]

It seems spreadsheets & dinosaurs on a collision course. How else can you explain Jon’s XKCD Velociraptor problem solved with Excel and now this. Debby, alert reader of our blog sent me this email yesterday.

I need an algebraic formula to solve this in Excel

I have 5 heads, 5 bodies, 4 arm sets, 4 leg sets and 3 tails. I need to see if I can create 1000 dinosaurs from these, and if that’s too many AND I need the 5 digit groupings to prove it and create them.
basically Xa*Xb*Xc*Xd*Xe=1000 – I’m not supposed to go over 1200. […] And then I want the 5 digit combinations if possible – right now they are trying to do the combinations by hand – would be awesome if we could do it in Excel.

Continue »

CP051: VLOOKUP FAQs – Most frequently asked questions about VLOOKUP – Answered

Published on Jan 7, 2016 in Chandoo.org Podcast Sessions
CP051: VLOOKUP FAQs – Most frequently asked questions about VLOOKUP – Answered

In the 51st session of Chandoo.org podcast, let’s discuss most frequently asked questions about VLOOKUP.

What is in this session?

In this podcast,

  • What is VLOOKUP?
  • What happens when VLOOKUP can’t find the value?
  • Should my list be sorted?
  • Is VLOOKUP slower than INDEX + MATCH?
  • What if my list has multiple matches?
  • How to fetch 2nd / 3rd matching item?
  • How to fetch all matching items?
  • How to fetch items matching multiple conditions?
  • How to speed up VLOOKUP?
  • Why doesn’t my VLOOKUP work?
  • What to do in case of errors?
  • Resources for you
Continue »

2016 Calendar, daily planner Excel templates [free downloads]

Published on Jan 4, 2016 in Learn Excel

Here is a New year gift to all our readers – free 2016 Excel Calendar & daily planner Template.

download-free-2016-calendar-daily-planner-templates

This calendar 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 »

How many Mondays between two dates? [homework]

Published on Dec 18, 2015 in Excel Challenges
How many Mondays between two dates? [homework]

Here is a quick but challenging homework problem for you.

Let’s say you have two dates – Start and End.

And you want to find out how many Mondays are there between those two dates (including the start & end dates).

What formula would give the answer?

Please post your formulas / VBA functions / DAX measures in the comments section.

Continue »

CP050: Fifty Excel Tips to make you awesome

Published on Dec 3, 2015 in Chandoo.org Podcast Sessions
CP050: Fifty Excel Tips to make you awesome

This is going to be epic!!! In the 50th session of Chandoo.org podcastwe have 50 Excel tips to make you awesome.

What is in this session?

In this podcast,

  • Thank you message
  • Fifty tips in 5 buckets
    • Shortcuts & Productivity
    • Formulas
    • Managing Data
    • Charts
    • Using Excel better
Continue »

Pricing Tier Lookup formula

Published on Dec 1, 2015 in Excel Howtos, Learn Excel
Pricing Tier Lookup formula

Here is an interesting twist on the good old VLOOKUP. How to find the pricing applicable for given quantity of a product?

Something like above.

Looks interesting? Then read on…

Continue »

Can you extract numbers from text – homework

Published on Oct 30, 2015 in Excel Challenges, Learn Excel

Here is a quick homework to keep you busy this weekend.

Can you extract number of days from below text.

Nov15 PUTS (23 days)
March15 TIKS (3 days)
March1 TIKS (25 days)
June11 TIKS (10 days)

Assume the data is from cell A1.

Your solution should return the following:
23
3
25
10

Post your answers (formulas, VBA code or Power Query M code) in the comments.

Continue »

CP047: Best Excel tools for Entrepreneurs

CP047: Best Excel tools for Entrepreneurs

In the 47th session of Chandoo.org podcast, let’s see how Excel can make you an awesome entrepreneur.

What is in this session?

In this podcast,

  • Why Excel for entrepreneurs
  • Key areas of a business owner’s work
    • Projects & to dos
    • Finances
    • Customers & marketing
    • Planning & strategy
    • Processes & workflows
  • 5 features of Excel that help
  • Conclusions
Continue »

Formula Forensics No. 038 – Find Which Worksheet a Max or Min Value is located on

Published on Oct 14, 2015 in Formula Forensics, Huis, Posts by Hui
Formula Forensics No. 038 – Find Which Worksheet a Max or Min Value is located on

Learn how to find which worksheet a max or min value occurs on using this neat formula

Continue »

Use NUMBERVALUE() to convert European Number format

Published on Oct 6, 2015 in Excel Howtos, Learn Excel

If you deal with customers or colleagues in Europe, often you may see numbers like this:

  • 1.433.502,50
  • 9.324,00
  • 3,141593

When these numbers are pasted in Excel, they become text, because Excel can’t understand them.

Here is a simple way to convert the European numbers to regular ones.

Use NUMBERVALUE() Function.

Continue »

Weighted Sorting in Excel [video]

Published on Oct 1, 2015 in Excel Howtos, Learn Excel
Weighted Sorting in Excel [video]

Imagine you are looking customer data like below and want to sort them by performance. If you sort the data by any one column, you will not get full picture of performance. To understand which customers rank low on performance, you need to defined a weighed sort, the kind of sort where you assign weights to each attribute (customer age, recent purchases and rate of returns) and come up with single score to sort them all.

Sounds interesting? Watch below video to understand how to do weighted sorting in Excel.

Continue »

How countries spend their money – chart alternatives

Published on Sep 22, 2015 in Charts and Graphs

Econimist’s daily chart is a one of my daily data porn stops. They take interesting data sets and visualize in compelling ways. While the daily chart page is insightful, sometimes they make poor charting choices. For example, this recent chart visualizing how countries spend their money uses a variation of notorious bubble chart. Click on the chart to enlarge.

20150912_woc650_1

What is wrong with this chart?

Bubble charts force us to measure and compare areas of circles. Unless you have a measuring tape somehow embedded in your eyes and you are a walking human scientific calculator, you would find this task impossible.

So when you look at the chart and want to find out what percentage Japanese spend on restaurants or how much Americans pay for housing, your guesses will have large error margins.

Not only bubble charts are difficult to read, they are very hard to align. So when you have a bunch of bubbles, no matter how hard you try, your chart looks clumsy (see how the Russian food bubble eats in to Mexico’s bubble, as if it is too hungry 😉 )

Let’s check out a few alternatives to this chart. Read on…

Continue »

Case Sensitive Lookups

Published on Sep 7, 2015 in Excel Howtos
Case Sensitive Lookups

We all know that VLOOKUP (and its cousins MATCH, HLOOKUP and LOOKUP) are great for finding information you want. But they are helpless when you want to do a case-sensitive lookup.

So how do we write case sensitive VLOOKUP formulas?

Simple. We can use EXACT formula.

Continue »

Weekend poll: Formulas or Pivot Tables?

Published on Sep 4, 2015 in Learn Excel

Time for a quick weekend poll. What is your favorite tool for data analysis?

  • Formulas
  • Pivot Tables
  • Or both

Post your choice in the comments. Also mention the number of years Excel experience you have.

For ex, my answer is: Both (10 years)

Continue »

Build models & dashboards faster with Watch Window

Published on Aug 30, 2015 in Learn Excel, VBA Macros
Build models & dashboards faster with Watch Window

Here is a familiar scenario: You are building a dashboard. Naturally, it has a few worksheets – data, assumptions, calculations and output. As you make changes to input data, you constantly switch to calculations (or output) page to check if the numbers are calculating as desired. This back and forth is slows you down.

Use Watch Window to reduce development time.

Continue »