Ms Excel Formula Errors
Ms Excel Formula Errors:
Ms Excel Formula Errors & functions :When creating a formula, it is possible that you make a mistake and Excel would not be able to evaluate it correctly. The following errors many appear when creating formula.
| Error Message | Meaning |
| #DIV/O! | A division by zero has occurred in the formula |
| #N/A | A value is not available to the formula |
| #NAME? | An unrecognizable range name is used in the formula |
| #VALUE! | An incorrect argument or operator is used in the formula |
| #REF! | An invalid cell is referenced in the formula |
Copying formula in Excel:
You can copy formula to other cells. In the below figure, we have used the formula “=B2+C2+D2” in E2. It calculated the total of Roll No 1. We can copy the same formula in E3,E4,E5 and E6 to calculate the totals of all students.
- Click on E2.
- Drag the fill handle to E6.
- Release the mouse.
The formula will be copies to all fields from E3 to E6. The totals of all students will be calculate automatically in the corresponding cells.
You will notice that the formula changes as you copy it to a different column or row. In the above example, the original formula in E2 was =B2+C2+D2. When you copy the formula to E3,E4, E5 and E6, the formula will automatically change according to the corresponding rows. For Roll no 2 the formula is automatically changes o “=B3+C3+D3” and so on…..
Functions:
Functions are built-in formulas that are used to perform complicate calculation. And Functions can be a more efficient way of performing mathematical operations than formulas. So Excel provides more than 300 functions that are divided into ten separate categories.
- Financial
- Date & time
- Math &trig
- Statistical
- Lookup & reference
- Database
- Text
- Logical
- Information
- Engineering
Different functions are use for different purposes the functions starts with an equal sign =and then function mane including any arguments in parentheses. The functions are not case sensitive but it is better to write them in uppercase. A function can use one or more arguments. Each function is use for one specific purpose.
Example
The following example shows a function with an argument. The function SUM adds the values in the cells from B3 to B10
=SUM(B3:B10)
Excel basic functions
Several other functions and examples are given in the table below:
| Function | Example | Description |
| SUM | =SUM(A1:100) | Finds the sum of cells A1 through A100 |
| AVERAGE | = AVERAGE (B1:B10) | Finds the average of cells B1 through B10 |
| MAX | =MAX(C1:C100) | Highest number from cells C1 to C100 |
| MIN | =MIN(CD1:D100) | Lowest number from cells D1 to D100 |
| SQRT | =SQRT(D10) | Square root of the value in Cell D10 |
| TODAY | =TODAY() | Current date |
Using argument within functions:
Most functions contain one or more arguments in parentheses. Multiple parameters should be separate with commas. And A parameter name with two words should include an underscore a num-char. Excel used various types of arguments from different types of information. Some functions can have up to 30 arguments.
Entering a function:
The following procedure is use to enter a function
- Click in the cell where the function to be entere.
- Click insert function button from Formula Bar. A dialog box appears:
- Select the category of function from Function Category list OR click. All category. The Function name list will display functions for the selected category.
- Scroll down the list of function to find the required function and click to select it.
- Click OK. The Paste function dialog will disappear. Excel displays the worksheet with the following dialog box: