All articles with 'choose()' Tag

Dynamic Print Areas

Published on Aug 24, 2015 in Excel Howtos
Dynamic Print Areas

Yesterday, you learned about Print Areas – a time & paper saving feature of Excel. While print areas are great, you can only set up one print area per sheet. What if you want to print either report or data based on user selection? 

In such cases, you can set up dynamic print areas. 

That is right. See above demo to understand how it looks. Read on to learn how to set up dynamic print areas.

Continue »

CP028: How to tell business logic & rules to Excel?

Published on Jan 22, 2015 in Chandoo.org Podcast Sessions, Random
CP028: How to tell business logic & rules to Excel?

In the 28th session of Chandoo.org podcast, let’s figure out how to express business rules & logic to Excel.

What is in this session?

What good are spreadsheets if they can’t solve business problems?

But we all struggle when it comes to modeling real world business conditions in Excel. For example, if you have below business rule to decide how much discount to offer a customer,

  • If the customer bought 3 or more times previously and offer 15% discount
  • If the customer bought 1 or 2 times previously AND customer’s age is >40, offer 10% discount
  • If the customer visited our New York store between 6PM-9PM offer 5% discount
  • Else no discount

How would you go about modeling these in Excel?

That is our topic for this podcast session.

In this podcast, you will learn

  • The challenge of modeling business logic & rules in Excel
  • My struggles with such formulas in early days
  • 4 features of Excel that can help you with this.
  • Example business rules & how to write formulas
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 »

Charmed Price Problem

Published on Oct 6, 2014 in Excel Howtos
Charmed Price Problem

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.

Continue »

CHOOSE() me, an introduction to Excel CHOOSE function

Published on Jul 16, 2014 in Learn Excel
CHOOSE() me, an introduction to Excel CHOOSE function

Today lets learn about Excel CHOOSE() function.
CHOOSE eh? What does it do?
To understand CHOOSE() and appreciate its uses, lets invent an imaginary boos-subordinate pair.

Jasmine is the boss. She is, well, lets call her peculiar. She likes olives, Tuesdays & color Red. She hates potatoes.

Martin is the faithful butler of Jasmine. He is obedient, quirky and tall. He likes lotuses, Fridays & color blue. He hates potassium.

Enter Jasmine’s scarf problem:

Jasmine likes to wear a different colored scarf every weekday. She likes to wear Red colored scarf on Mondays & Tuesdays. She likes to put on the blue polka dot scarf on Wednesdays. On Thursdays, she wears her olive colored scarf. On Fridays & Saturdays, she prefers the lovely orange blue scarf. Sundays are no scarf days.

No wonder she is peculiar.

Continue »

How to create an Interactive Chart in Excel? [Tutorial]

Published on Apr 23, 2013 in Charts and Graphs, Excel Howtos
How to create an Interactive Chart in Excel? [Tutorial]

Imagine you have a worksheet with lots of charts. And you want to make it look awesome & clean.

Solution?

Simple, create an interactive chart so that your users can pick one of many charts and see them.

Today let us understand how to create an interactive chart using Excel.

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 No. 028 – It’s Just a Jump to the Left

Published on Sep 6, 2012 in Formula Forensics, Huis, Posts by Hui, Random
Formula Forensics No. 028 – It’s Just a Jump to the Left

VLookup is often seen as the poor cousin in the lookup function family and often gets overlooked when it comes to looking up values due to a common misconception that Vlookup doesn’t or can’t lookup values to the left of the reference value.
Today at Formula Forensics we will explode this myth and see how to make VLookup, lookup to the Left and we’ll explain how it works.

Continue »

Dummy Data – How to use the Random Functions

Published on May 4, 2011 in excel apps, Excel Howtos, Huis, Learn Excel, Posts by Hui
Dummy Data – How to use the Random Functions

Don’t know which way to go ?
Can’t make up your mind between alternatives?

Using collected or known data is the best when developing Excel models, but from time to time this may not be available when you are developing your model. This post will look at some options for setting up Dummy Data using Excels Random number functions.

Continue »

How to Find Dates of Public Holidays using Excel

Published on Dec 24, 2009 in Learn Excel
How to Find Dates of Public Holidays using Excel

Lets celebrate these holidays in PHD Style. By learning few excel formulas that you can use to find out dates for some of the popular public holidays like – labor day, memorial day etc. When is Labor Day (US) in 2010? Labor day (the US variant) is celebrated on first Monday of every September. It […]

Continue »

Findout Thanksgiving Day’s Date for Any Year [Excel Formulas]

Published on Nov 25, 2009 in Learn Excel
Findout Thanksgiving Day’s Date for Any Year [Excel Formulas]

Every year, on 4th Thursday of November, folks in US celebrate Thanksgiving day. A similar holiday exists in Canada too, they celebrate it on Second Monday of Every October.We will celebrate thanksgiving in PHD style, by sharing a wacky formula tip.

Today, we are going to learn how to use excel formulas to find out thanksgiving day’s date for any year.

Continue »

Learn Excel IF Formula – 5 Tricks you Didnt know

Published on Jun 9, 2008 in Featured, Learn Excel
Learn Excel IF Formula – 5 Tricks you Didnt know

Excel has various functions, including functions to calculate inverse cosine of a given value, to multiply 2 matrices, to estimate the internal rate of return. But, most of us(well, just me then..) use just about 5-6 formulas to do our jobs. And IF formulas are a majority of these, so it doesn’t harm to learn […]

Continue »