Formula

It is the most used facility of MS-EXCEL. A formula is a cell entry that calculates values to return a result. Each Excel formula must have three key elements: the equal sign (=) that signifies that the entry is a formula, the values or cell references to be calculated, and the mathematical operators, such as a plus sign (+) for addition or a minus sign (-) for subtraction.

All Excel formulas must begin with the equal sign. The equal sign tells Excel that the entry is a formula. If your formula begins without an equal sign, Excel treats it as a regular cell entry and doesn’t perform the calculation.

Entering data in cell

Types of entries

The information or data we enter in a cell can be of the following types

Text entries

The text entries in Excel start with a single inverted quote (‘). The default alignment of a text entry is left. Alphanumeric entries are all taken as text entries.

Numeric entries

The numeric entries in Excel start with a number and all the characters in this type should consist of the numbers. The default alignment of the numeric entry is right.

Formula entries

The formula entry in Excel start with a plus (+) sign or an equal to (=) sign.

Date entries

The date entry in Excel is right aligned, and date when entered will change into the preset date format.

Enter numbers, text, a date, or a time

Text or date or time can be typed in a blank cell as

• Click the cell where we want to enter data.
• Type the data and press ENTER or TAB.

Use a slash or a hyphen to separate the parts of a date; for example, type 9/5/2002 or 5-Sep-2002. To enter a time based on the 12-hour clock, type a space and then a or p after the time; for example, 9:00 p. Otherwise, Microsoft Excel enters the time as AM

Operators

When you create a formula in Excel, you need to include an operator. All formulas must contain mathematical operators so that Excel knows what calculation to perform.

Operator	What It Does
+	Addition
–	Subtraction
*	Multiplication
/	Division
=	Equal to
<	Less than
<=	Less than or equal to
>	Greater than
>=	Greater than or equal to
<>	Not equal to
%	Percentage
^	Exponentiation

Figure below shows a very simple formula. The intent is pretty clear—the formula asks Excel to add 20,000 to 1,000.

Formula in cell D4

Order of Operations

In a simple formula, Excel performs only one calculation. However, formulas can often contain instructions to perform multiple calculations. If you were to talk out a more complicated formula, you might say something like this: “Add together the price of a car that costs Rs. 20,000 and a truck that costs Rs.18,000 and then multiply the combined price by 5% sales tax to determine the sales tax due on the combined cost of the vehicles.”

You might think that you’d enter the value for the formula this way

=20,000+18,000*.050

However, if you typed that formula in a cell, the formula would be incorrect. The reason is that Excel uses something called operator precedence to perform calculations. Operator precedence determines the order in which calculations are performed. Calculations are performed from left to right in the following order

• All operations enclosed in parentheses
• All exponential operations
• All multiplication and division operations
• All addition and subtraction operations

The best way to force Excel to calculate your formulas correctly is to use parentheses. Group the values and operators that you want to calculate first in parentheses. For example, the formula

(20,000+18,000)*.050

It tells Excel to first add the numbers within the parentheses and then to calculate the sales tax percentage on the total. The parentheses tell Excel to perform the addition first and then multiply the sum by the tax percentage.

You can even nest parentheses within parentheses to further break down how you want Excel to calculate your formula. Just remember that each opening parenthesis must have a closing parenthesis. If your formula does not contain the required number of parentheses, Excel displays an error message

Formula Error

Formulas and their components

A formula is an entry that performs a calculation. The result of the calculations is displayed in the cell containing the formula always begins with an equal sign, which defines a numeric entry. A formula is an equation that performs operations on worksheet data. Formulas can perform mathematical operations, such as addition and multiplication, or they can compare worksheet values or join text. Formulas can refer to other cells on the same worksheet, cells on other sheets in the same workbook, or cells on sheets in other workbooks. A formula is also defined as, a sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).

Formulas use the following arithmetic operators to specify the type of numeric operation to perform:

In a formula that contains more than one operator, Excel performs the calculations in the following order of precedence

• Exponentiation
• Multiplication and division
• Addition and subtraction

This order can be overridden by enclosing the operation we want performed first in parenthesis. Excel evaluates operation in parenthesis working from the innermost set of parenthesis out. For example, in the formula =5*4-3, excel first multiplies 5 times 4 to get 20, and then subtracts 3 for a total of 17. If we enter the formula as =5*(4-3), Excel first subtracts 3 from 4 because the operation is enclose in parenthesis. Then Excel multiples the result by 5. If two or more operators have the same order of precedence, calculations are performed in order from left to right.

The values on which a numeric formula performs a calculation are called operands. Numbers or cell references can be operands in a formula. Usually cell references are used, and when the numeric entries in the referenced cells change, the result of the formula is automatically recalculated. We can also use single-word row and column headings in place of cell references and formulas.

Steps involved to write the formulas are given below

• Click the cell in which we want to enter the formula.
• Type = (an equal sign). If we click Edit Formula (the equal to sign on the formula bar figure 10) or Paste Function (a buttons with the symbol fx, in the standard toolbar) Microsoft Excel inserts an equal sign for we.
• Enter the formula.
• Press ENTER.

Totaling rows and columns using Auto Sum

We can sum a range of cells automatically by using AutoSum. When we select the cell where we want to insert the sum and click AutoSum, Microsoft Excel suggests a formula. To accept the formula, press ENTER.

To change the suggested formula, select the range we want to total and press ENTER.

Autosum icon

Autosum selection

Display the current date

Current date can be displayed by the today function. This function returns the serial number of the current date. The serial number is the date-time code used by Microsoft Excel for date and time calculations. This function is used as TODAY ().

Functions

Functions are prewritten formulas that perform certain types of calculations automatically. The syntax or rules of structure for entering all functions is

Function name (arguments1, argument2…)

The functions name identifies the type of calculations to be performed. Most functions require that we enter one or more arguments following the function name. An argument is the data the function uses to perform the calculation. The type of data the function requires depends upon the type of calculation being performed.

Using Arguments

Arguments are values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numeric values, text values, cell references, ranges of cells, names, labels, and nested functions.

Using Functions Wizard to create formulae

Following steps are taken to create formulae

• Click the cell in which we want to enter the formula.
• To start the formula with the function, in insert menu click function.
• Type function name or select by selecting “All” in select by category drop down list
• Click the function we want to add to the formula.
• Enter the arguments.
• When we complete the formula, press ENTER.

Insert function dialog box

Pasting Functions

Paste function feature simplifies entering functions by prompting us to select a function from a list and then helps us to enter the arguments correctly. The function category list box displays the names of the functions in the selected category. The currently selected category is Most Recently Used. The category displays the names of the last 10 functions used.

The Formula Palette dialog box is displayed to help we enter the arguments required for the selected function. The upper section displays the proposed argument range in the number1 text box. The lower section describes the function and what the function requires for the arguments.