Working with Formula in Excel 2010

The worksheet would be nothing more than a plain tabular representation of data if there are no formulas. A formula is a set of instructions that must be typed into a cell. It performs some calculations and then displays the result in the cell.

To deal with values and text, formulas work a number of operators and worksheet functions. Formula values and text can be found in other cells, making data changes simple and giving worksheets their complex existence.

You can easily modify the data in a worksheet, for example, and formulas work.

Elements of Formulas

The formula can consist of any of the following elements:

• Mathematical operators, such as + (for addition) and * (for multiplication)

Example:

• =A1+A2 Adds the values in cells A1 and A2.
• Values or text Example:
• =200*0.5 Multiplies 200 times 0.15. This formula uses only values, and it always returns the same result as 100.
• Cell references (including named cells and ranges) Example:
• =A1=C12 Compares cell A1 with cell C12. If the cells are identical, the formula returns TRUE; otherwise, it returns FALSE.
• Worksheet functions (such as SUM or AVERAGE) Example:
• =SUM (A1:A12) Adds the values in the range A1:A12.

Creating Formula

You need to type in the Formula Bar to build a formula. The ‘=’ sign starts the formula. You can either type in the cell addresses or point to them in the worksheet while manually creating formulas. Providing cell addresses for formulas using the Pointing method is also a simpler and more efficient method of formula creation. When using built-in functions, you define the function’s arguments in the Function Arguments dialog box by clicking or dragging through the cell set you want to use. Take a look at the image below.

When you finish entering a formula, Excel calculates the answer, which is then shown within the worksheet cell (the contents of the formula, however, continue to be visible on the Formula bar anytime the cell is active). If you make a formula error that prevents Excel from calculating the formula at all, Excel shows an Alert dialog box with instructions on how to correct the problem.

Copying Formulas in MS Excel 

In a spreadsheet that is largely based on formulas, copying formulas is one of the most common tasks. When a formula uses cell references instead of constant values, Excel makes copying an original formula to any position where a similar formula is needed much easier.

Relative Cell Addresses    

MS Excel takes care of it for you, changing the cell references in the original formula to match the location of the copies you produce. This is accomplished using a method known as relative cell addresses, in which the column references in the cell address in the formula adjust to their new column location, and the row references adjust to their new row position.

Let us use an example to demonstrate this. If we want the total of all the rows at the end, we’ll write a formula for the first column i.e. B. We want sum of the rows from 3 to 8 in the 9th row.

After writing formula in the 9th row, we can drag it to remaining columns and the formula gets copied. After dragging we can see the formula in the remaining columns as below.

• Column C : =SUM(C3:C8)
• Column D : =SUM(D3:D8)
• Column E : =SUM(E3:E8)
• Column F : =SUM(F3:F8)
• Column G : =SUM(G3:G8)

Cell References in Formulas    

The majority of the formulas you write contain references to cells or ranges.

These references allow your formulas to interact with the data in certain cells or ranges in real time.

If the formula applies to cell C2 and you adjust the value in C2, the formula result automatically represents the new value. If you didn’t use references in the formulas, you’d have to adjust the values in the formulas themselves if you wanted to change them.

There are three types of references you may use in a formula when referencing a cell (or range): relative, absolute, and mixed references.

Relative Cell References

The cell references which change when copying a cell formula because they are offset from the current column and row. Excel generates relative cell references in formulas by default.

Absolute Cell References

When you copy the formula, the row and column references do not shift because the reference is to the actual cell address. References in this study use two dollar signs to indicate the column letter and the row number (for example, $A$5)

Mixed Cell References

The reference row or column is relative and the other is absolute. Just one element of the address is absolute (e.g. $A5 or A$5).