All articles with 'if() excel formula' Tag

IF is the most used Excel function out there. Here are 10 advanced IF tricks to take your formulas to next-level ?

Do you want to create a simple, elegant and useful tracker using Excel? You can make trackers with features like tables, data validation rules and conditional formatting. In this page, I will explain the process for creating an Excel tracker.

Over on Facebook, Kristin asks, Help, my blood pressure is going thru the roof. I can’t seem to solve this blood pressure problem. 

Let’s simplify Kristin’s problem.

You have some data in the format shown above.

And you want to find out the BP category for each reading, using some rules. Read on to solve the problem.

Here is a simple IF formula challenge for you. Go ahead and post your answers in the comments section. Can this person vote in Sumeria? Imagine you are the chief election officer in the great country of Sumeria. You have introduced a new eligibility criteria for voters just before the grand presidential elections of 2016. In order […]

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

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

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.

Sometime during the 2nd half of 2013, I finished 10 years of Excel usage. In the last 10 years, I completed my studies, got my first job, married, had kids, visited 15 different countries, quit my job to start a business, bought first car, first house, made dozens of new friends, read 100s of books, wrote a book and learned 1000s of new things. And all along, Excel stayed a true companion. Right from MBA entrance exam preparation in 2003 to making my summer internship project reports in 2005 to planning my wedding expenses in 2007 to getting a promotion in 2009 to planning my kids feeding schedule in 2010 to running a successful business in 2014, Excel helped me in every step.

So today, I want to tell you the top 10 things I learned using Excel in last decade. Grab a hot cup of coffee, buckle your belts and get ready for time travel.

Hi folks,

I have a quick announcement & an awesome Power Pivot technique to share with you. First the announcement.

Only few hours left to join our Power Pivot course…

As you may know, I have opened enrollments for our inaugural batch of Power Pivot course few days ago. The aim of this course is to make you awesome in Excel, Advanced Excel, Dashboards & Power Pivot.

We will be closing the doors of this program at midnight, today (11:59 PM, Pacific time, Friday, 15th of February).

If you want to join us, click here and enroll now.

How many people have joined the class?

At the time of writing this, we have 195 students enrolled in Power Pivot class. We are eager to share Power Pivot knowledge & techniques to as many more of you as possible. So go ahead and join us because you want to be awesome in Excel & Power Pivot.

Did you know What Happened at Last Coffee Day?
Mr. Bean “dressed up” the financial statements and was caught in the fraud. But he was the CEO of Latte! So why did he commit fraud in his own company??

Any Guess?

Take a cappuccino and I will give you a hint – How was Mr. Bean’s Bonus to be decided?

Read on to know more…

If I need some charting inspiration, I always visit New York Times. Their interactive visualizations are some of the best you can find anywhere. Clear, beautifully crafted and powerful. Long time readers of Chandoo.org knew that I like to learn from visualizations in NY Times & redo them using Excel.

Today let me present you one such chart. This is based on an interactive visualization prepared by NY Times explaining how the tax burden has changed over years for various income groups.

Take a look at tax burden chart – Excel implementation

Read on to learn how to create this chart using Excel.

Moosa, one of our readers emailed this interesting question:

I have huge list of customers (around 1500).
Table includes following information
Customer # , Customer Name, Sales 2002, sales 2003, … sales 2012

My requirements are
1. list of customer who did not have sales during all these years
2. List of customer who have not business from 2003
3. List of customer who have not business from 2004

Today, lets learn how to identify all the non-performing customers.

Can we make a calendar using Pivot Tables?!? Of course we can. Today let us learn a simple technique to create calendar style reports using Pivot tables. Thanks to Rob for inspiration Before making any progress, let me thank Rob from PowerPivotPro for the inspiration. Recently he wrote an article explaining how to use PowerPivot […]

If I were to hire an data analyst, I would simply ask them to write a complex IF formula in Excel. If they can write it, the interview progresses, else, they are out. In other words,

=IF(person_can_write_big_fat_IF_formula=TRUE, proceed_with_interview, say_thanks_and_call_next_person)

If you are able to write IF formulas for any situation, then you are bound to be awesome in Excel.

So, to test how well you know your IFs & Boolean functions, let me give you a small challenge.

While I was away, Hui did a splendid job of starting a new series called Formula Forensics. The idea is to break down formulas for difficult real-world problems so that we can understand them better. In that spirit, I am giving you an interesting and tough formula homework. Situation: Imagine you work for Large Fries […]

Many of us start using Excel to keep track of something. And along way, we realize that Excel has a powerful feature called formulas, using which we can automate a lot of things. BOOM! Before we realize, we are in the thick of VLOOKUPs and SUMIFs.

But, along way, we also pick up a few bad habits or believe a few myths. Today, lets bust 10 Excel formula myths that we hear often.

Read this post to learn what these myths and why you should not trust them.