Components of an Excel Formula
All Excel formulas begin with an equal sign. For example, the following is a formula — =4*2 (* tells Excel to perform a multiplication). A formula can be composed of cell references, numeric values, functions, operators, parenthesis etc. Lets see what thi
Cell reference
A cell reference refers to the spreadsheet cell address, which is indicated by a column letter and a row number. It tells Excel where to look for the values or data you want to use in a formula. You can write the cell references in different ways in a formula to tell Excel to act in different ways on the data.
i. Relative reference
Relative cell references are references to cells relative to the position of the formula. By default, all cell references in Excel are relative references.
For example, if you enter the formula =A1 in cell A3, Excel will look for the value of the contents of the cell two rows above the position of the formula, which is A1. This is a relative reference formula.
Now, if you copy this formula to another location, say A4, Excel automatically adjusts the references in the pasted formula to refer to a different cell relative to the new position of the formula. So, the formula in cell A4 will be =A2, which refers to the cell that is two rows above A4.
ii. Absolute Reference Format
Absolute references are cell references that always refer to cells in a specific location. That means, if you don't want Excel to adjust references when you copy a formula to a different cell, you will have to use an absolute reference. In the above example, if you don’t want Excel to adjust the cell reference, you can create an absolute reference to cell A1 by placing a dollar sign ($) before it. Your formula will now look like =$A1. Now, if you copy this formula to A4, it will not change to A2.
So, relative references automatically adjust when you copy them, and absolute references don't.
iii. Mixed reference
You can also use a mixed-reference format. Here, you can anchor either the cell's row (by placing the dollar sign in front of the row address only, for example, A$1), or its column (by placing the dollar sign in front of the column address only, as in $A1).
Value
This may refer to the numbers or text strings that form a part of the formula.
Functions
Excel comes with many built-in functions such as SUM, AVERAGE, MIN, MAX, etc.
Operators
This refers to the different symbols used to indicate a mathematical or logical function.
Thus, an Excel formula is a set of instructions that use cell references and operators to answer a question about data stored in the spreadsheet. For instance, Sean wants to know the average score of the students in Class IX. Has student A scored as many marks as student B? What will be the result of the combination of two strings such as soft and ware?
