1) What are the categories of Excel functions (at least 15)? Give 2 examples and their definitions for each category

2. What is the difference between Absolute cell reference and Relative cell reference? Explain mix cell references.

 

1. Excel Function Categories

• Compatibility Functions: These are still available for backwards compatibility.

Examples of functions:

CEILING (CEILING.MATH) - The number rounded up to the nearest integer or significant figure.

CHIDIST (CHISQ.DIST.RT) - The one tailed probability of the chi-squared distribution.

• Cube Function: These are used to extract data from Analysis Services cubes.

Examples of functions:

CUBEKPIMEMBER - The name of a key performance indicator.

CUBEMEMBER - The member or tuple from a cube.

• Database Functions: These are used to analyse, count, add and filter data stored in lists or databases.

Examples of functions:

DMAX - The maximum value in a database column satisfying certain conditions.

DMIN - The minimum value in a database column satisfying certain conditions.

• Date & Time Functions:These are used for entering and calculating dates and times.

Examples of functions:

DATEVALUE - The date serial number given a date in text format.

DAY - The day as an integer given a date serial number.

• Dynamic Array Formulas: These are used for returning an array of values from a single formula.

Examples of functions:

RANDARRAY - (Microsoft 365) The array of random numbers between 0 and 1.

SEQUENCE - (Microsoft 365) The array of sequential numbers.

• Engineering Functions: These are used to handle complex numbers, conversions and measurements.

Examples of functions:

BIN2HEX - The number converted from binary to hexadecimal.

BIN2OCT - The number converted from binary to octal.

• External Functions: These are used to retrieve data from external sources.

Examples of functions:

CALL - The results from a procedure in a DLL (dynamic linked library) or code resource.

EUROCONVERT - The number converted to euros.

• Financial Functions: These are used for depreciation, accrued interest, annuities, rates of return and future values.

Examples of functions:

ACCRINT - The accrued interest for a security that pays interest periodically.

ACCRINTM - The accrued interest for a security that pays interest at maturity.

• Information Functions: These are used to identify the contents of cells and determine any errors or blanks.

Examples of functions:

CELL - The text string indicating information about a cell.

ERROR.TYPE - The number corresponding to a particular error value in a cell.

• Logical Functions: These are used to perform conditional analysis and apply logical conjectures to your data.

Examples of functions:

AND - The logical AND for any number of arguments.

FALSE - The logical value False.

• Lookup & Reference Functions: These are used to locate references and specific values within tables of data.

Examples of functions:

ADDRESS - The cell reference given a row and column number.

AREAS - The number of areas in a cell range or reference.

• Maths & Trigonometry Functions: These are used for rounding, ranking, analysing trends and to calculate probabilities and logarithms.

Examples of functions:

ASIN - The arc-sine of a number.

ASINH - The hyperbolic arc-sine of a number.

• Matrix Functions: These are used for multiplying, transposing and finding the inverse and determinant of arrays.

Examples of functions:

MDETERM - The matrix determinant of an array.

MINVERSE - The inverse matrix of an array.

• Statistical Functions: These are used for standard deviation, distributions, square roots, average, median.

Examples of functions:

AVEDEV - The average deviation of the numbers in a list, table or cell range.

AVERAGE - The arithmetic mean of non blank cells in a list, table or cell range.

• Text Functions: These are used for entering, searching, replacing and manipulating text strings.

Examples of functions:

CHAR - The character with the corresponding ANSI/ASCII number.

CLEAN- The text string with all the non-printable characters removed.

• Web Functions: These are used for web services, xml and xpath.

Examples of functions:

CHAR - The character with the corresponding ANSI/ASCII number.

CLEAN - The text string with all the non-printable characters removed.

 

 

2. (a) What is the difference between Absolute cell reference and Relative cell reference? 

• There are two types of cell references: relative and absolute. Relative and absolute references behave differently when copied and filled to other cells. Relative references change when a formula is copied to another cell. Absolute references, on the other hand, remain constant no matter where they are copied.

(b)  Explain mix cell references.

• A mixed cell reference is either an absolute column and relative row or absolute row and relative column. When you add the $ before the column letter you create an absolute column or before the row number you create an absolute row. For example, $A1 is absolute for column A and relative for row 1, and A$1 is absolute for row 1 and relative for column A. If you copy or fill the formula across rows or down columns, the relative references adjust, and the absolute ones don't adjust.