IfError() Function

The Excel IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.

Syntax

The syntax for the IFERROR function in Microsoft Excel is:

=IFERROR( formula, alternate_value )

Parameters or Arguments

formula

The formula or value that you want to test.

value_if_error

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.

Excel Iferror Function Examples

Example 1

The following spreadsheet shows two simple examples of the Excel Iferror function.

Formulas:

	A	B	C
1	1	2	=IFERROR( A1 / B1, 0 )
2	1	0	=IFERROR( A2 / B2, 0 )

Results:

	A	B	C
1	1	2	0.5	- A1 / B1 produces no error so result 0.5 is returned
2	1	0	0	- A2 / B2 produces an error so the alternative value 0 is returned

Note that:

-          In the first example (in cell C1), the value argument, A1/B1 returns the value 0.5. This is not an error and so this value is returned by the Iferror function.

-          In the second example (in cell C2), the value argument, A2/B2 returns the #DIV/0! error. Therefore, the Iferror function returns the value of the value_if_error argument, which is 0.

Example 2

In the following spreadsheet the Excel Iferror function is used with the Vlookup function.

If the Vlookup function successfully looks up a value, this is displayed in the cell; Otherwise, the text "not found" is displayed.

Formulas:

	A	B	C	D
1	Lookup List		Jim's Class:	=IFERROR( VLOOKUP( "Jim", A2:B6, 2, FALSE ), "not found" )
2	Beth	Class 1	Mary's Class:	=IFERROR( VLOOKUP( "Mary", A2:B6, 2, FALSE ), "not found" )
3	Bob	Class 2
4	Alf	Class 2
5	Jim	Class 3
6	Ann	Class 3

Results:

	A	B	C	D
1	Lookup List		Jim's Class:	Class 3
2	Beth	Class 1	Mary's Class:	"not found"
3	Bob	Class 2
4	Alf	Class 2
5	Jim	Class 3
6	Ann	Class 3

Notes:

-          If formula is empty, it is evaluated as an empty string ("") and not an error.

-          If value_if_error is supplied as an empty string (""), no message is displayed when an error is detected.

-          If IFERROR is entered as an array formula, it returns an array of results with one item for each cell in value.

Reference:

https://exceljet.net/excel-functions/excel-iferror-function

http://www.excelfunctions.net/Excel-Iferror.html

https://www.techonthenet.com/excel/formulas/iferror.php

follow us on http://arivilm.blogspot.in

like us on https://www.facebook.com/Arivilm2501/

Excel Error Messages

If Excel detects that your Excel Formula or Function contains an error, it will return an error message (e.g. #VALUE!, #N/A).

The error message that you are presented with, provides information about the type and cause of the Excel formula error. It can therefore assist you with identifying and fixing the problem.

The notes below provide a quick reference guide of what each of the different error messages means.

#####

When you see ##### displayed in your cell, it can look a little scary. The good news is that this simply means the column isn't wide enough to display the value you've inputted. And that's any easy fix!

How to Resolve This Error: 

Click on the right border of the column header and increase the column width.

Also you can double-click the right border of the header to automatically fit the widest cell in that column.

#DIV/0

Excel displays the #DIV/0! error when a formula tries to divide a number by 0 or an empty cell.

How to Resolve This Error:

This error is pretty easy to resolve. Simply change the value of the cell to a value that is not equal to 0 or add in a value if your cell was blank. Here's an example:

#N/A

Indicates that a value is not available to a formula, typically means that the value you are referring to in your formula cannot be found.

You may have accidentally deleted a number or row that's being used in your formula, or are referring to a sheet that was deleted or not saved.

For advanced users, one of the most common causes of the #N/A error is when a cell can't be found from a formula referenced in a VLOOKUP.

How to Resolve This Error:

Triple check all your formulas and be sure to look closely at which sheets or rows may have been deleted or incorrectly referenced. If you have a few formulas linked together, check to see that everything in every formula has a value.

#NAME?

Occurs if Excel does not recognize a formula name or does not recognize text within a formula.

This error value appears when you incorrectly type the range name, refer to a deleted range name, or forget to put quotation marks around a text string in a formula.

How to Resolve This Error:

Triple check all your formulas & references and be sure to look closely at which sheets or rows may have been deleted or incorrectly referenced.

#NULL!

Occur when you specify an intersection of two areas that don't actually intersect, or when an incorrect range operator is used.

To give you some additional context, here's how Excel reference operators work:

·         Range operator (semi colon): Defines a references to a range of cells.

·         Union operator (comma): Combines two references into a single reference.

·         Intersection operator (space): Returns a reference to the intersection of two ranges.

How to Resolve This Error:

First things first, check to make sure that you are using the correct syntax in your formula.

·         You should be using a colon to separate the first cell from the last cell when you refer to a continuous range of cells in a formula.

·         On the other hand, you should be using a comma should when you refer to two cells that don't intersect.

#NUM!

If your formula contains numeric values that aren't valid, you'll see an #NUM! error appear in Excel. Often times this happens when you enter a numeric value that's different than the other arguments used in your formula.

For example, when you're entering an Excel formula, make sure you don't include values like $1,000 in currency format. Instead, enter 1000 and then format the cell with currency and commas after the formula is calculated.

How to Resolve This Error:

Check to see if you have entered any formatted currency, dates, or special symbols. Then, make sure to remove those characters from the formula, only keeping the numbers themselves. 

#REF!

This error occurs when you delete a cell referred to in the formula or if you paste cells over the ones referred to in the formula.

That means that you may have accidentally deleted or pasted over a cell that was used in your formula. For example, let's say that the "Outcome" column references the formula: =SUM(A2,B2,C2).

If we were to accidentally delete the "Number 2" column, we'd see this error:

How to Resolve This Error:

Before you paste over a set of cells make sure that there are no formulas that will be affected. Also, when deleting cells it’s important to double check what formulas are being referred in those cells.

If you accidentally delete a few cells, you can click the Undo button on the Quick Access Toolbar (or press CTRL+Z for PC / Command + Z for Mac ) to restore them.

#VALUE!

This error is most often the result of specifying a mathematical operation with one or more cells that contain text.

How to Resolve This Error:

An easy solution to this error is to double check your formula to make sure that you used numbers only. If you're still seeing an error, check for blank cells, missing formulas linking to cells or any special characters you may be using.

Using The Error Alert Button

When a formula yields an error value (other than #N/A) in a cell, Excel displays a green triangular error indicator in the upper-left corner of the cell, and an alert options button appears to the left of that cell when you make it active.

If you position the mouse pointer on that options button, a ScreenTip appears, describing the nature of the error value. Also, a drop-down button appears to its right that you can click to display a drop-down menu with the following options:

·         Help on This Error: Opens an Excel Help window with information on the type of error value in the active cell and how to correct it.

·         Show Calculation Steps: Opens the Evaluate Formula dialog box where you can walk through each step in the calculation to see the result of each computation.

·         Ignore Error: Bypasses error checking for this cell and removes the error alert and Error options button from it.

·         Edit in Formula Bar: Activates Edit mode and puts the insertion point at the end of the formula on the Formula bar.

·         Error Checking Options: Opens the Formulas tab of the Excel Options dialog box, where you can modify the options used in checking the worksheet for formula errors.

Reference:

http://www.excelfunctions.net/Excel-Formula-Error.html

http://www.dummies.com/software/microsoft-office/excel/understanding-excel-2007s-formula-error-values/

http://www.excel-easy.com/functions/formula-errors.html

https://blog.hubspot.com/marketing/common-excel-error-messages

follow us on http://arivilm.blogspot.in

like us on https://www.facebook.com/Arivilm2501/

Stdevpa() Function

The Excel STDEVPA function calculates the standard deviation for an entire population. Unlike the STDEVP function, STDEVPA evaluates text and logicals that appear in references.

Syntax

The syntax for the STDEVPA function in Microsoft Excel is:

=STDEVPA( value1, value2, ... value_n )

Parameters or Arguments

value1

A population value that can be a number, text, or a logical value. A value that is TRUE is evaluated as 1. A value that is FALSE or a text value is evaluated as 0.

value2, ... value_n

Optional. They are the population values that can be numbers, text, and logical values. There can be up to 30 values entered.

Stdevpa Function Example

The following spreadsheet shows the Excel Stdevpa function used to calculate the standard deviation of the set of values in cells A1-A4 and in cells A1-A6.

Formulas:

	A	B
1	1	=STDEVPA( A1:A4 )
2	3	=STDEVPA( A1:A6 )
3	5	=STDEVPA( A1:A4, 1, 0 )
4	2
5	TRUE
6	text

Results:

	A	B
1	1	1.479019946
2	3	1.632993162
3	5	1.632993162
4	2
5	TRUE
6	text

Note that, in the spreadsheet above, the function in cell B2 includes the values in cells A5 and A6. In this case:

►   The logical value TRUE in cell A5 is treated as the value 1;

►   The text in cell A6 is treated as the value 0.

This is shown by the example in cell B3, in which cells A1 - A4 and the values 1 and 0 give the same result as the standard deviation calculation for cells A1 - A6.

Reference:

https://www.techonthenet.com/excel/formulas/stdevpa.php

http://www.excelfunctions.net/Excel-Stdevpa-Function.html

https://exceljet.net/excel-functions/excel-stdevpa-function

follow us on http://arivilm.blogspot.in

like us on https://www.facebook.com/Arivilm2501/