Downloaded 43 times
Uploaded byMuhammad Yasir Bhutta
Functions in MS Excel
Cell references in Excel identify cells by their column letter and row number. Relative cell references adjust automatically when copied to another cell, while absolute references remain fixed with dollar signs ($) before the column letter and/or row number. Named ranges provide meaningful labels for cells or ranges. Functions perform calculations using arguments, which can be values, cell references, or other functions. Error values indicate issues like division by zero or invalid data.
More Related Content
Similar to Functions in MS Excel
Functions in MS Excel
- 1.
- 2. A cellreference refers to a cell or a range of cells on a worksheet. It identifies a cell by referring to the row and column headings. When referring to a cell, enter the column letter followed by the row number Example A10 refers to the cell at the intersection of column A and row 10 yasirbhutta@uaf.edu.pk
- 3.
- 4. The cells incolumn A and row 1 A1 The range of cells in column A A1:A20 and row 1 through row 20 All the cells in row 5 5:5 All the cells in column k k:k All the cells in column k through m k:m The range of cells in column A A10:D20 Cell B2 on Sheet2 =Sheet2!B2 yasirbhutta@uaf.edu.pk
- 5. When youcopy a formula, Excel adjusts each cell reference in the formula relative to the position of the formula That is, by default a copy operation maintains the same relationship relative to the new cell yasirbhutta@uaf.edu.pk
- 6.
- 7. Cell Reference 1. Relative 2. Absolute 3.Mixed yasirbhutta@uaf.edu.pk
- 8. In Excel,a relative cell reference identifies the location of a cell or group of cell. By default, a spreadsheet cell reference is relative. If copy the formula ,the reference automatically adjusts. yasirbhutta@uaf.edu.pk
- 9.
- 10.
- 11. A referenceto a particular cell or group of cells that does not change, even copy the reference to another cell. An absolute cell reference consists of the column letter and row number surrounded by dollar sign($). To place a $ sign before a Column letter and Row number of a Cell click on the cell that contains the absolute value and press Key yasirbhutta@uaf.edu.pk
- 12.
- 13.
- 14. You createa mixed cell reference when you set either the column or row reference fixed instead of both being absolute › Absolute row reference -If a dollar sign were to precede only the row number, Example A$1, then only the column reference changes relatively when the formula is copied yasirbhutta@uaf.edu.pk
- 15. › Absolute columnreference -If a dollar sign precedes only the column letter, e.g. $A1, Excel will change only the row reference relative to the change in the formula location yasirbhutta@uaf.edu.pk
- 16. A nameis a meaningful shorthand that makes it easier to understand the purpose of a cell reference, constant, formula or table. You can create a name that describes a cell or range You can also use the labels of columns and rows on a worksheet to refer to the cells within those columns and rows. yasirbhutta@uaf.edu.pk
- 17.
- 18. Formulas >Defined Names > Create from Selection yasirbhutta@uaf.edu.pk
- 19. Excel hashundreds of predefined formulae known as Functions Functions use specific arguments in a particular order or structure The arguments of functions can be anything from numbers, text, logical values, or cell references You can also have formulae or other functions as arguments in a function that are called nested functions yasirbhutta@uaf.edu.pk
- 20. The normalorder for a function is:- › Function Name, › The opening parenthesis, › Arguments for the function separated by commas and closing parenthesis. =SUM(C7:C10) Functio n Name Argument Parenthesis yasirbhutta@uaf.edu.pk
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36. Error Message Meaning ######This error occurs when a column is not wide enough, or a negative date or time is used. # 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 yasirbhutta@uaf.edu.pk
- 37.
- 38.
- 39. Note You canenter #N/A in those cells where data is not yet available. Formulas that refer to those cells will then return #N/A instead of attempting to calculate a value. yasirbhutta@uaf.edu.pk
- 40.
- 41.
- 42.