Microsoft Excel Creating Formulas - Microsoft Excel

Formulas in MS Excel

• The Bread and butter of worksheet are the formulas.Worksheet is a simple tabular representation of data without formula.A formula consists of special code to be entered into a cell.The calculations are performed and the results are returned which are displayed in the cell.
• Formulas use a variety of operators and worksheet functions to work with values and text.The values and text used in formulas can be located in other cells.The data changes are made easy and gives worksheets their dynamic nature. For example, the data can be changed quickly in a worksheet and formulas works.

Elements of Formulas

A formula can consist of any of these 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 SUMor AVERAGE)

Example :

• =SUM(A1:A12) Adds the values in the range A1:A12.

Creating Formula

• Create a formula in the Formula Bar. Formula begins with '=' sign. Either type the addresses in the cell or point them in the worksheet when building the formulas manually.
• Pointing method is used to supply the cell addresses for formulas is often easier and more powerful method of formula building.
• Using built-in functions, click the cell or drag through the cell range that is used when defining the function’s arguments in the Function Arguments dialog box.

The below screenshot illustrates the following. • After completing the formula entry, Excel calculates the result and displayed inside the cell within the worksheet.
• If an error in made in the formula that prevents Excel from being able to calculate the formula , Excel displays an Alert dialog box suggesting how to fix the problem.
Microsoft Excel Topics