Avoiding Error Displays when Formulas Return Result Calculations

To avoid errors being displayed, combine the IF formula with the ISERROR formula:

1. In cell A1, type the number 100.
2. In cell B1, type the formula =A1/A2. The calculation returns an error #DIV/0!, which occurs when trying to divide a number in a cell by 0 (A2).
3. To avoid displaying this error in a cell, in cell C1, enter the formula =ISERROR(B1). The formula returns the result TRUE. That is, cell B1 contains an error in the calculation of the formula (the ISERROR formula is located in the Information category in the Paste Function dialog box).
4. In cell D1, enter an IF formula =IF(C1,0,B1).
5. Combine the formulas into one nested formula by copying the formula (without the = sign) from cell C1, and pasting it into cell D1 (see tip # 196).
6. From cell B1, copy the formula (without the = sign), and paste it twice.

The result is one nested formula: =IF(ISERROR(A1/A2),0,A1/A2)।

Add Combo Box to a Sheet in Microsoft Excel

• 
  
  
  
  What are the Advantages of working with Combo Box?
  
  Choosing an item from a list in the Combo Box returns a number. The number then replaces the use of the Match formula to calculate the column number in the Vlookup formula (read tip) and replaces the use of the Match formula (read tip) to calculate the column number in the Index formula.
  
  Working with the Combo Box
  
  A list of the cell ranges in the sheet is attached to the Combo Box. After an item is selected from the list, the Combo Box enters into the linked cell the ordinal number of the item that is selected on the list.
  
  
  
  Example:
  
  The screen shot displays a profit and loss statement as compared to the previous year. Changing the month in the Combo Box will automatically change the display of data according to the selected month.
  
  
  Step 1: Define names
  
  1.Enter the months list - January-December into the cells A1 to A12 in the new worksheet.
  
  2.Select cells A!:A12, press Ctrl+F3, enter the text MonthsList into the Names In workbook box, and click OK.
  
  3.Select Cell B1, press Ctrl+F3, enter the text MonthNumber into the Names in Workbook box, and click OK.
  
  
  Step 2: Add a Combo Box to a worksheet
  
  1. Select one of the toolbars, right-click, and select the Forms toolbar.
  
  2. Copy the Combo Box by clicking the Combo Box icon, and then release the mouse. Create a rectangle with the mouse in the worksheet, and then release the mouse.
  
  
  Step 3: Format the Combo Box
  
  1.Select the Combo Box; right-click; and from the shortcut menu that appears, select Format Control. Then select the Control tab
  
  2.In the Input range box, type the name MonthsList (You cannot press F3 to paste a name with an object).
  
  3.In the Cell link box, type the name MonthNumber.
  
  4.Click to select the 3-D shading box (more aesthetic).
  
  5.Click OK.
  
  Exit the formatting mode of Combo Box, and select a cell in the sheet. Open the list of items in the Combo Box, and select a month. Note that the new month number is shown in cell B1.
  
  
  
  
  Advantages of working with names in a Combo Box
  
  Attaching a list with Define Names causes your list references to be updated automatically in the Combo Box. Sorting the list in the sheet will automatically sort the list in Combo Box as well.
  
  
  Deleting a Combo Box
  
  Select the object; right-click; and from the shortcut menu that appears, select Cut.
  
  
  Adjusting the size of the Combo Box to the cells
  
  Select the Combo Box and right-click; the Combo Box is now in editing mode. Adjust the width or height of the Combo Box by dragging one of the corner boxes; adjust the placement of the Combo Box on the worksheet by clicking anywhere inside the box and dragging it.
  
  
  Automatic adjustment of the Combo Box
  
  Hold down the Alt key while you click the mouse to adjust the size of the Combo Box.
  
  Screenshot // Add Combo Box to a Sheet in Microsoft Excel