Showing posts with label Check Formula. Show all posts
Showing posts with label Check Formula. Show all posts

Solutions for VLOOKUP not working or showing #N/A

Major reasons for your VLOOKUP not working properly or showing error (#N/A or #REF!) could be:
  1. The Lookup values have Trailing space at the end
  2. The Numbers entered have been formatted as Text
  3. You forgot to reference lock your table array
  4. The file or table array to lookup has been deleted
So, let's tackle these issues and get solution for each of them.
1. Lookup values having trailing space at the end
Let's look at the below VLOOKUP example. Although, the values are available in the source table, the lookup table is not returning values for some of them. The problem is not directly evident. To see the problem, go to the lookup value in the source table and click the cursor at the very end of value. The extra space will be evident at the end of the input.
Solution: It is very simple. Please remove the extra space by pressing "Backspace" in edit mode.
2. The numbers entered have been formatted as Text (shows #N/A error)
In case none of the lookup value are not returning results (#N/A is appearing), it may be possible that the formatting of source is incorrect (the numbers will appear to be aligned left).  Click on the lookup reference of the data set and check in the formula bar if there is an apostrophe before your entry.
This is sometimes intentionally done to populate leading zero in the number (although a better way is through custom formatting...!!).
Solution: Again, the solution is very simple. Multiply all your lookup values by 1. This will convert all of them into number format (the numbers will get automatically aligned right).
3. You forgot to reference lock your table array
This is very common problem that occurs when we drag the VLOOKUP formula after entering it in one cell. Often we forget to lock the table array and as a result the lookup values you are trying to match are no longer part of original lookup array (since the lookup array also shifts down when we drag it).
Solution: Before dragging the VLOOKUP formula, press F4 key to lock lookup array (in edit mode)
4. The file or table array to lookup has been deleted (shows #REF! error)
Sometimes the source data used for VLOOKUP function gets deleted or sheets are improperly moved without reference. In such case VLOOKUP shows #REF! error.
Solution: Try and locate the source data and verify the linking of VLOOKUP formula again

How to use Scenario Manager in Excel 2013

Scenarios form an important part in real-world problems. The inputs used in the real-world problems are dynamic and hence lead to different outputs. Scenario Manager in Excel help us in getting solutions of these problems by accounting for changing inputs. Before we create a scenario, we must design the worksheet so that it contains at least one formula that is dependent on the input cells.
Once various scenarios are created based on different input values, Excel provides facility to create a detailed summary report of these scenarios.
Let's understand this on the basis of Costing example. The example calculates Net revenue of a coffee shop.
Formula: Net revenue = Gross Revenue - Total Variable Cost - Total Fixed Cost
Where, Gross Revenue = (Number of coffee cups sold *Selling Price per unit);                                 Total Variable Cost = (Number of coffee cups sold*Variable cost per unit)
Let's name these input cells as Number_Units_Sold, Selling_Price_Unit, Variable_Unit, Total_Fixed
Go To: "DATA" tab in the ribbon>> What-If Analysis>>>Scenario Manager [Shortcut: Alt+AWS]
CREATING SCENARIOS:
1. Click on the "Add" button in the Scenario Manager dialog box.
Insert Scenario name as "Base Case" and select all the input values (C2:C5) in Changing Cells field. In case the input values are scattered, we can hold CTRL key while clicking on different input cells OR use comma after clicking on each input cell.
2. Click "OK". This will open Scenario Values dialog box.
Insert various input values corresponding to this scenario as shown above.
3. Click "Add" to insert new scenario OR click "OK" to stop adding scenarios.
Once all the scenarios are added, the Scenario Manager dialog box will open again and show names of all the entered scenarios.
4. To see result of a particular scenario, click on the Scenario name and press "Show" button.
Excel will automatically change input values as listed in the scenario and show relevant output.
IMPORTANT NOTE:
  • Always assign range names to your input cells before you begin creating the various scenarios. It is easy to recognize the input filed by names while using the Scenario Values dialog box to enter input values for various scenarios.

How to Trace Precedents and Dependents in Excel 2013

Trace Precedents and Dependents in Excel_Intro
Excel offers excellent formula auditing tools for tracking down a cell that is causing error by tracing the relationships between the formulas in the cells of spreadsheet. By tracing the relationships, we will be able to locate the Precedents and Dependents.

Also read: How to use Goal Seek feature in Excel 2013
Precedents are cells or ranges of cells that affect the active cell value.
Dependents are cell or ranges of cells that are affective by the active cell.
Trace Precedents and Trace Dependents are powerful tools under Formula Auditing in Excel. Go to:       "FORMULAS" tab in the ribbon>>>Functions under Formula Auditing [Alt+M]
  • Trace Precedents [Alt+MP]: When we click this button when the active cell contains formula, it draws arrows from the primary cells which are directly affecting the formula in the active cells (direct precedents). On clicking the button once again, it shows more arrows from cells which are affecting direct precedent cells and so on.
  • Trace Dependents [Alt+MD]: When we click this button, it draws arrows from the selected cell to the cells (direct dependents) that use, or depend on, the results of the formula in the selected cell. On clicking the button once again, it shows "tracer" arrows identifying the cells (indirect dependents) that refer to formulas found in the direct dependents.
  • Remove Arrows [Alt+MA]: On clicking this button, all the arrows created by precedents and dependents are removed. Additionally, Excel also gives options of customized removal of arrows by providing two buttons in the drop-down besides Remove Arrows.
  1. Remove Precedent Arrows [Shortcut: Alt+MAP] - Removes only precedent arrows
  2. Remove Dependent Arrows [Shortcut: Alt+MAD] - Removes only dependent arrows
IMPORTANT NOTES:
  • Double-clicking an arrow activates the cell on one end of the line. Double-clicking again activates the cell on the other end.
  • If the relationship shown by arrows are in the same worksheet, then Excel draws red or blue arrows. It shows black dotted arrow with miniature icon of worksheet in case the relationship exists with another worksheet (in same or different workbook).