Search

All articles with 'if() excel formula' Tag

Convert ISERROR formulas to IFERROR formulas [macro]

Published on Mar 14, 2011 in VBA Macros

Last Friday, we have learned about an interesting formula – IFERROR Formula using which you can easily handle errors in Excel workbooks.

Quite a few people reading that page asked, “Wow, this is good. But how can I take a sheet full of =IF(ISERROR(…)….) formulas and convert them to =IFERROR()”

There is a different set of folks who asked “Wow, this is good. But quite a few of my colleagues use Excel 2003 and they see a bunch of #NAME errors when I send them an excel workbook with IFERROR formulas. Any help?!?”

I am pleased to announce that I wrote 2 simple macros, iferror2iserror() and iserror2iferror() that would scan formulas in a bunch of selected cells and convert them from IFERROR to ISERROR and vice-a-versa.

Continue »

IFERROR Excel Formula – What is it, syntax, examples and howto

Published on Mar 11, 2011 in Learn Excel

If `IFERROR()` were to be a person, I would hug her so hard that Jo (my wife) would get in to a cat fight with her. I know many a woman (and man) who get in to a fight with Excel formulas often. But thankfully, we avoid that as IFERROR is not a real person. It is, however a darned useful formula.

Since I cannot hug a formula anymore than I can get my son to sit tight, I will go ahead and sing an ode to her, in my style – by writing about how useful and powerful IFERROR formula is.

Continue »

Excel Animation without Macros!

Published on Nov 30, 2010 in Charts and Graphs, Learn Excel

Today we will learn an interesting animation technique that ONLY uses, … wait for it …, Excel Formulas. That is right, we will use simple formulas to animate values in Excel.

Intrigued? Confused? Interested?

First see a short demo of excel animation achieved using this technique.

Continue »

How to make a Birthday Reminder in Excel ? [Video]

Published on Oct 26, 2010 in Excel Howtos

Learn how to create a birthday reminder worksheet in excel in this video post. You can also download a template to keep track of upcoming birthdays and anniversaries.

Continue »

Excel Links – Happy Birthday Edition

Published on Oct 20, 2010 in excel links

First some personal matters. Today I am celebrating my 28th birthday. The last one year has been very good for us. We have been very busy parenting 2 hilarious and naughty twins, I moved back to India, quit my job and started a company. My business became a mild success crossing \$100k revenues in 12 […]

Continue »

Finding 2nd Largest Item in a List that meets a Criteria [Excel Array Formulas]

Published on Oct 8, 2010 in Learn Excel

Excel Array Formulas have subtle beauty and raw power. Today I want to share how you can use Excel Array Formulas to find-out the 2nd largest sale amount corresponding to a particular product.

Lets say you have sales data like this:
Now, we all know that, to get 2nd largest sale amount, we can use the LARGE() formula. Like this: =LARGE(RANGE,2)

But, how do we know what is the 2nd largest sale amount for the product “bricks”?

Continue »

How to cook a delicious dynamic chart that will have your boss drool

Published on Aug 31, 2010 in Charts and Graphs, Featured

Dynamic charts are like my favorite food, Mangoes. They tempt, tease and taste awesomely. In this post, we are going to learn how to create a dynamic chart using check boxes and formulas as shown in the animation aside. Are you ready for some excel chart cooking?

Continue »

Find-out Days Overlaped [Excel Formula Homework]

Published on Jul 7, 2010 in Featured, Learn Excel

Few days back we have learned how to find if 2 sets of dates overlap using Excel Formulas. For eg. we have learned that to check whether {1 July,2010 to 23 July 2010} overlaps with the date range {15 July, 2010 to 16 August 2010}, we can use a formula like, =if(or(and(x>=a,x<=b),and(a>=x,a<=y)), “Overlap”,”Do not overlap”) […]

Continue »

Between Formula in Excel [Quick Tips]

Published on Jun 24, 2010 in Excel Howtos

Checking if a value falls between 2 other values is fairly common when you are working with data. In today’s quick tip, we are going to learn how to check for such between conditions in excel. You will be learning how to check if a value, date or text falls between 2 other values, dates or texts (when arranged in dictionary order) as shown in the picture aside.

Continue »

Check if two ranges of dates overlap [Excel Formulas]

Published on Jun 1, 2010 in Learn Excel

While preparing a project plan, I had a strange problem. I wanted to highlight all the project tasks that fall with-in a certain date range. At the lowest level, the problem is like this: There are 2 ranges of dates (a,b) and (x,y) and I want to know if they overlap (ie at least one […]

Continue »

Display Alerts in Dashboards to Grab User Attention [Quick Tip]

Published on May 25, 2010 in Charts and Graphs

Dashboards can be overwhelming with lots of details and context. A simple way to drag user’s attention to important stuff in the dashboard is to use alerts. See this example to understand what alerts mean. How to display alerts in Excel Dashboards? The easiest way to display alerts is to use Excel 2007’s Conditional Formatting […]

Continue »

Published on Apr 8, 2010 in Charts and Graphs

We all know that legend can be added to a chart to provide useful information, color codes etc.

Today we will learn how to make the chart legends smarter so that they provide more meaning and context to the chart, like this:

This type of legend can be more useful than a plain legend as this provides more useful information without taking up too much space.

Continue »

How to Check whether a Table is Filtered or not using Formulas

Published on Mar 29, 2010 in Excel Howtos, Learn Excel

Let us start the week with a simple formula (well, to be fair, let us start the week with a strong cup of coffee, then this formula).

Often when we have large data sets, we apply data filters to select and display only information we want to see.

Some of you know that whenever we apply filters on a dataset, we can look at status bar area to find out if any filter is applied on the current worksheet.

But, what if you need a way to show “filtering” status thru formulas? Like this…,

Continue »

Writing “Either Or” formula in Excel [Formula Howtos]

Published on Mar 2, 2010 in Excel Howtos, Learn Excel

We all know the AND, OR & NOT formulas in Excel using which you can perform simple logical operations And, Or & Negate. But what if you are the chief of HR at ACME Company, where they have a strange rule on extra allowance like this: Now, to calculate the dates in a month that […]

Continue »

Find and Remove Blank Items from a Range of Cells [personal experience]

Published on Feb 23, 2010 in Excel Howtos

Most of you know that during day time I work as a business analyst. Today while preparing some test scenarios for our latest insurance application, I came across a weird problem. There are some steps in testing. For each test scenario, a combination of these steps is required. It is my responsibility to identify the […]

Continue »

Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.