IFERROR Function

In my previous article, I discussed about the different types of errors we get in Microsoft excel while working with numbers, text etc. These errors we get in Excel maybe it is #N/A or #VALUE! or #REF! (or #DIV/0! or #NUM! or #NAME? or #NULL and so on) is very irritating while analyzing data because it will not allow you to perform basic operations like SUM, SUMIF or SUBTOTAL etc. (it will show errors).

Therefore, Excel has given us one function named IFERROR to overcome such situations. 

Today I will discuss about IFERROR function with few examples.

The IFERROR function is a built-in function in Excel that is categorized as a Logical Function. 

Syntax

The syntax for the IFERROR function in Microsoft Excel is:

=IFERROR(value, value_if_true)

Parameters or Arguments

value

The ‘value’ may be a formula or simply division or anything which has an outcome.

value_if_true 

The alternate value that is returned if the formula results in an error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL). Otherwise, the function will return the result of the formula if no error occurs.

If you remember the functionality of IF function, then it will be very easy to understand. ‘value_if_true’ is the outcome you want to display or printed IF THERE IS ANY ERROR in the ‘value’ parameter.

Let us have two examples and I hope the functionality of this function will be very clear.

Examples :

In the below image, I want to show you a very small calculation on Target vs Achievement in percentage, it is Achievement divided by Target. But you know that whenever I want to divide some value with a Zero it will return an undefined value as output (which is the basic rule in mathematics) and in Excel it is division error (#DIV/0!).

How to use IFERROR Function in Excel

Similarly, whenever I try to divide some numeric value by some Text, it will also report me an error in Excel as #VALUE!.
Now, if I use IFERROR Function the problem will disappear. What I do in my daily data analysis for very long formula is that I first type the formula and then I move at the beginning of the formula after ‘=’ sign, type IFERROR and one opening bracket and then I move to the end of the formula and type a comma and usually I put a ‘0’.

In place of zero you can use anything you wish, like ‘OUTPUT-ERROR’ etc.

Note : If you are comfortable using VLOOKUP by typing at the formula bar or within the Cell, you can start typing like “=IFERROR(VLOOKUP(…….),0)”

In the below example, I will show you how you can use IFERROR function in VLOOKUP.

How to use IFERROR Function with VLOOKUP Function in Excel

There are two tables, one is TABLE – 1 which is the data provider, and another is TABLE – 2, data to be mapped here from TABLE – 1.

If you check carefully at TABLE – 1, you will notice there are four names A, B, C & D whereas in the TABLE -2 name field contains names as A, X, P & D.

In this situation whenever I try to map TABLE - 2 using VLOOKUP the X & P will display #N/A errors because of unavailability of the data. But if I use IFERROR function before the VLOOKUP function it will replace the #N/A by the value I need (here I opted for “VALUE NOT FOUND”)

Hope this article is enough for you to understand the IFERROR function. If any question arises, feel free to comment in the comment box below.

Thanks for reading…

Now Function

In this article, I will discuss about a very tiny but very powerful function in the category Date/Time Function and it is NOW Function.

Although it is a very small function in Microsoft Excel but you can list this function in your time saving function lists.
So, let’s see how to use this useful function.

Description
The Microsoft Excel NOW function returns the current system date and time.

The NOW function will refresh the date/time value whenever the worksheet recalculates which means it will refresh the date & time wherever you type something in the excel sheet. 

Note : To stop this feature of NOW Function go to Formula menu, at the end you will find Calculation tab, click on Calculation Option, select Manual from Automatic. This will prevent entire Excel from refreshing or Calculating, therefore you other formula may not work, so after you are done don’t FORGET to switch this option from Manual to Automatic.

Calculation Option

The spreadsheet will continue to display this value, until the worksheet recalculates. By pressing the F9 key, you can force the worksheet to recalculate and the NOW function will update its value.

Syntax

The syntax for the NOW function in Microsoft Excel is as under.

=NOW()

Parameters or Arguments

There are no parameters or arguments for the NOW function.

Returns

The NOW function returns a date & value.

Example :

How to use Now Function

Hope you have enjoyed this article...
Thank you for reading... 

Year Month Day Function in Excel

Friends,

The usual date format is DD-MM-YYYY or MM-DD-YYYY in Excel (Depending upon the Regional Settings in your PC) but while preparing data to be analyzed we may need some more information from the date provided.

For example, given a range of dates in the format DD-MM-YYYY  (22-07-2017) we may need to get the number of month or the year in the next column, today we'll discuss how to separate the Day or the Year or the Month ?

There are three built in functions in Microsoft Excel - Day(), MONTH() & YEAR().

Today we'll know how to use these functions in Excel with few examples. These functions are very useful and are very small and also very easy to remember.

How to use Day function in Excel ?

Syntax

The syntax for the DAY function in Microsoft Excel is

=DAY(date_value)

Parameters or Arguments

date_value : A valid date to return the day.

Returns

The DAY function returns a numeric value between 1 and 31.

How to use Month function in Excel ?

Syntax

The syntax for the MONTH function in Microsoft Excel is

=MONTH(date_value)

Parameters or Arguments

date_value : A valid date to return the month.

Returns

The MONTH function returns a numeric value between 1 and 12.

How to use Year function in Excel ?

Syntax

The syntax for the YEAR function in Microsoft Excel is

=YEAR(date_value)

Parameters or Arguments

date_value : A valid date to return the month.

Returns

The YEAR function returns a numeric value between 1999 and 9999.

So, that was the easy part, as example you can watch the picture below.

Day Month Year Function

Now if you make a little tweak in the above functions it can show you the immense power of these functions. Lets' discuss it with some example.

Suppose you need the month from a date given as 22-08-2017, if you use MONTH function you will get the output as '8', but you need 'August' to be printed here. How to do it ?

Option 1 : Use IF function, like =if(month(A1)=1, "Jan",if(month(A1)=,"Feb",....,"Do something")

This is tedious but it will work.

Option 2 : There is another FUNCTION (TEXT())to make this job very easy which I will discuss in my blog.

Till then...

Keep reading and keep growing your knowledge in Excel...

Thank you for reading...

Today Function

Hi friends,

In my last discussion I've shown you how to use the DATE Function, a very basic function; today I'll discuss about the 'TODAY' Function which is frequently used in daily data analysis.

Description

The Microsoft Excel TODAY function returns the current system date. This function will refresh the date whenever the worksheet recalculates/opened.

The TODAY function is a built-in function in Excel that is categorized as a Date/Time Function. You can use TODAY function as part of a formula in a cell of a worksheet.

Syntax

The syntax for the TODAY function in Microsoft Excel is very simple.

=TODAY()

Parameters or Arguments

There are NO parameters or arguments for the TODAY function.

Returns

The TODAY function returns a serial date value (a serial date is how Excel stores dates internally)

Example

The function is very easy because you don't need to enter any parameter or argument in this function. Just type '=today' and open and close bracket '()', press enter and you are done.

If you need to ADD or SUBTRACT days from today, simply type the TODAY function and either ADD or SUBTRACT the number of days you want to add or subtract like,

=TODAY()-5 (if the current date is 10-03-2018, he result would be 05-03-2018).

If you directly subtract today from any provided date, the result will be the DAY difference (in number). e.g.,

=10-08-2017 - TODAY()

Latter I'll show you few examples in next articles whenever necessary.

Please Note : The spreadsheet will continue to display this value, until the worksheet recalculates i.e., let suppose if you had entered date using this function yesterday (like : 09-03-2018)  and if you open your excel workbook next day, the date will automatically be refreshed to current system date (like : 10-03-2018).

Hope you have enjoyed this article.

Keep reading and keep growing your knowledge in Excel...

Thank you for reading...