FORMULAE IN EXCEL

Definition:

A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already available in Excel.

For example, cell B3 below contains a formula which adds the value of cell B2 to the value of cell B1.

Figure: Addition formula in excel

Enter any number in B1 cell and B2 cell then, type the formula in B3 cell as "=(B1+B2)" and press enter button. We will get the result in the B3 cell. Instead of adding two numbers, we can also subtract or multiply or divide the two numbers.
The formula will be as follows.
In B3 cell, type "=(B1-B2)".
or similarly, =(B1*B2) and =(B1/B2).

Figure: Applying formula in different ways

Applying Function:

Every function has the same structure. For example, SUM(A1:A4). The name of this function is SUM. The part between the brackets (arguments) means we give Excel the range A1:A4 as input. The function adds the values in cells A1, A2, A3 and A4. It's not easy to remember which function and which arguments to use for each task. Fortunately, the insert function feature in excel helps you with this.

To insert a function, execute the following steps.

1. Select a cell.
2. Click the insert function button.

Figure: Listing the numbers

Now click on fx button, "Insert Function" dialog box appears.
Search for a function or select a function from a category. For example, choose COUNTIF from the Statistical category.

Figure: Insert function Dialog box

Then Click OK.
The 'Function Arguments' dialog box appears.
Click in the Range box and select the range A1:C2.
Click in the Criteria box and type >5.
Click OK.

Figure: Function Arguments dialog box

Result will be as the number of cells that are higher than 5.

Figure: Result of D1 cell

In this way we have some functions like,

• AVEDEV(number1,number2) Returns the average of the absolute deviations of data points from their mean. Arguments can be numbers or names, arrays, or references that contain numbers.
• AVERAGE(number1,number2,...) Returns the average (arithmetic mean) of its arguments, which can be numbers or names, arrays, or references that contain numbers.
• AVERAGEA(value1,value2,....) Returns the average (arithmetic mean) of its arguments, evaluating text and FALSE in arguments as 0; TRUE evaluates as 1. Arguments can be numbers, names, arrays, or references.
• AVERAGEIF(range, criteria, average_range) Finds average(arithmetic mean) for the cells specified by a given condition or criteria.
• AVERAGEIFS(average_range, criteria_range, criteria,...) Finds average(arithmetic mean) for the cells specified by a given set of conditions or criteria.
• CORREL(array1,array2) Returns the correlation coefficient between two data sets.
• COUNT(value1,value2,....) Counts the number of cells in a range that contain numbers.
• COUNTA(value1,value2,...) Counts the number of cells in a range that are not empty.
• COUNTBLANK(range) Counts the number of empty cells in a specified range of cells.
• COUNTIF(range,criteria) Counts the number of cells within a range that meet the given condition.
• COUNTIFS(criteria_range, criteria,...) Counts the number of cells specified by a given set of conditions or criteria.
• DEVSQ(number1,number2,...) Returns the sum of squares of deviations of data points from their sample mean.
• EXPON.DIST(x,lambda, cumulative) Returns the exponential distribution.
• MAX(number1,number2,...) Returns the largest value in a set of values. Ignores logical values and text.
• MIN(number1,number2,....) Returns the smallest number in a set of values. Ignores logical values and text.
• MEDIAN(number1,number2,...) Returns the median, or the number in the middle of the set of given numbers.

Notes: