How to avoid #DIV/0! and #Value! Error?

 

To avoid the error of #DIV/0! and #Value! in your calculation result use functions IFERROR.

 

IFERROR returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. Use the IFERROR function to trap and handle errors in a formula. 

 

 

 

 

In the given example, you can see anything divided by 0 gives #DIV/0! and any alphabet divided by a number given results in an error #VALUE!. Now you can see that the summation of column D leads to an error #DIV/0!.

To avoid this use IFERROR(Write your formula as you did in Column D) i.e., =IFERROR(B2/C2) and copy the same formula to the entire column.

By doing this you can see that the error in row 5 and 6 are handled by the IFERROR function and the final summation of the column is E also returns a value instead an error.

 

For more information on IFERROR you can reach me at Samrat.biswaas@gmail.com.

 

 

About #N/A Error

This error indicates that a value is not available to a function or formula.

Symptom

Excel displays #N/A in one or more cells on a worksheet.

Causes

• Data is missing, and #N/A or NA() has been entered in its place.
• An inappropriate value was given for the lookup_value argument in the HLOOKUP, LOOKUP, MATCH, or VLOOKUP worksheet function.
• The VLOOKUP, HLOOKUP, or MATCH worksheet function was used to locate a value in an unsorted table.
• An array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.) is using an argument that is not the same number of rows or columns as the range that contains the array formula.
• One or more required arguments were omitted from a built-in or custom worksheet function.
• A custom worksheet function that you use is not available.
• A macro that you run enters a function that returns #N/A

 

For more details on #N/A Errors do write me at Samrat.biswaas@gmail.com

Use of Trace Precedents and Trace Dependents

Trace Precedents and Trace Dependents

In MS Excel, based on our need we apply formulas to different cells. While applying formula many a times we fetch value from different cells in the same worksheet. For ex: If I want to sum two values available in the existing worksheet then I will click equals to (=) and then write A1+B1=C1(selected cell where you want the sum of these two). In this the value of C1 is precedent of A1 and B1.

 

The example given above is very simple. Either I can select cell C1 to see the cells/values used for getting the sum in the formula bar. Or else I can select and click F2 to see which cells are used to fetch this data.

 

Now imagine you have a big worksheet. In which you have numerous different formulas included in it. Some of the values are taken from Cell B1 and some from Z100 etc. By using the above two options it is sometime very difficult to know that from where you have taken the value for a specific formula. It is often very difficult to scroll and check. So unless you know from where your specific formula/cell has taken values, you may accidentally delete some value which will lead to errors like #N/A, #Value, and #DIV/0.

 

Here comes the use of Trace Precedents in Formula Ribbon's Formula Auditing tools.
 

Step 1: Select the cell in which you want to check the Precedents and then click "Trace Precedents". Clicking this tool will provide you all the cells from where the data is fetched and used in a formula.



Step 2: Similarly click "Trace Dependents" in the same cell, this will help you know other values in the sheet which are dependent on this value.

For more details on Trace Precedents and Dependents you can write me at Samrat.biswaas@gmail.com

 

How to create One Variable Data Table using What-if-Analysis?

One Variable Data Table

To create a one-variable data table, you enter a series of input values either across columns of a single row or down the rows of a single column. The formulas in a one-variable data table refer to only one input cell: a row input cell for input values entered across a row or a column input cell for input values entered down a column.

Example: Say you need to find out the projected sales for year 2013. For this you have total sales value of 2012 and expected growth percentage in year 2013.  To get the projected sales for 2013 you apply a formula which includes the above value and factored in.

Now to find out if the total projected sales varies from 14% to 17% then how much will be the total sales for 2013 (with respect to the growth above percentages)? That is, to perform what-if analysis if the sales varies from 14% to 17%.

Step 1: Create a small table with your available values and apply formula {=$B$2+(B2*B3)-(B2*B4)} in cell B5. You can apply any formula. Data table in one variable only if a formula is used to find out the required value.

 

Step 2: Copy the original formula entered in cell B5 into cell E1. The copy of the original formula (into which the series of different growth rates in D2:D5 is to be substituted) is now the column heading for the one-variable data table.

Step 3: Select the cell range D2:E5. The range of the data table includes the formula along with the various growth rates.

Step 4: Choose What-If Analysis => Data Table in the Data Tools group on the Data ribbon. Excel opens the Data Table dialog box.

Step 5: As the different growth rates are in column so click in the Column Input Cell text box and then click cell B3. Excel inserts the absolute cell address, $B$3, into the Column Input Cell text box. Click OK. By doing this Excel calculates the projected sales value in column E.

 

 

 

 

 

So column D is the different expected growth percentages and column E is having the projected sales value with respect to different percentages.

 

For more details on this write to samrat.biswaas@gmail.com