Formulas and Functions
Excel formulas are used to perform calculations on data entered into the spreadsheet. Excel formulas start with an equal sign. Compound formulas involve more than one operator in a formula. These formulas are used to do simple as well as complex calculations. The user can also perform addition on character and string data types. Other operations such as subtraction, multiplication, division etc. are not allowed. The ‘&’ symbol is used for addition. Range is a rectangular area which consists of a group of cells.
A cell reference refers to a particular cell or range of cells in the worksheet. There are three types of references: relative reference, absolute reference and mixed reference. Absolute reference is used when the user doesn’t want to change the cell address while copying formula to another cell. It is designated in a formula by adding a dollar sign ($). It can precede the column reference, the row reference, or both. A mixed reference is a combination of absolute and relative cell references. The dollar sign ($) is used in the cells to indicate that a row number or column letter is to remain the same when copied and transferred from one cell to another. There are two ways to give the reference of one worksheet in another worksheet: using Copy-Paste command or using Sheet Reference. Functions are pre-defined formulas in Excel to perform simple and complex calculations. Some commonly used functions are SUM, AVERAGE, MIN, MAX, SQRT, COUNT, INT, ROUND, POWER, MOD, EXP and TODAY.