There are many functions in Excel. Functions are used to extract meaningful data on a range of data. E.g. the Autosum uses the SUM function to add up a block of cells. The formula created is

=SUM(B3:B9)

The formula included the function word (SUM) and the range of cells which to apply the function, in this example B3:B9, enclosed within brackets. There are a number of ways to insert formulas in to a function, most notably from the Function Library on the Formulas tab.

Mathematical Functions

SUMIF -This function adds up a given block of cells that meets specified criteria. E.g: Add all the sales figures of the Manchester Branch.

To insert the SUMIF Function
1. Select the cell for the formula
2. From the Function Library on the Formulas tab, select the Maths & Trig library.
3. From the list of functions, select SUMIF.

 

 

 

4. The function’s dialog box opens.
a. Range-is the range of cells that you want evaluated by criteria. In the above example, the range of cells where the criteria is located is C3:C9
b. Criteria-is the criteria in the form of an expression, or text that defines which cells will be added. In the above example, the criteria is (including the quotation marks) “Manchester”
c.  Sum_range- are the actual cells to add if their corresponding cells in range match criteria. In the above example, the actual data is located in B3:B9.

 

 

SUMIFS

Where Multiple Criteria is required to be met, use the SUMIFS Function.
In the above example, to calculate the total sales of all reps in Manchester with individual sales of £15,000 or over.

1. Select the cell for the formula
2. From the Function Library on the Formulas tab, select the Maths & Trig library.
3. From the list of functions, select SUMIFS
4. The function’s dialog box opens.
a. Sum_range- are the actual cells to add if their corresponding cells in range match all the criteria. In the above example, the actual data is located in B3:B9.
b. Criteria_Range1-is the range of cells that you want evaluated by the first criteria specified in Criteria1. In the above example, the range of cells where the criteria is located is C3:C9.
c. Criteria1-is the first criteria in the form of an expression, or text that defines which cells will be added. In the above example, the first criteria is (including the quotation marks) “Manchester”.
d. Repeat steps b & c for each additional criteria. Up to 127 criteria and criteria ranges are allowed.
?
Statistical Functions
Statistical function can be found in the More Functions Library.
AVERAGE
To find the average of a block of cells:
1. Select the cell for the formula
2. From the Function Library on the Formulas tab, select the More Functions library and then Statistical
3. From the list of functions, select Average.
4. The function’s dialog box opens.
a. Number1- the range of cells to find the average of
b. Up to 255 ranges can be added in Number2….Number255
AVERAGEIF
This function finds the average of a given block of cells that meets specified criteria. E.g: Find the average sales figures of the Manchester Branch.
To insert the AVERAGEIF Function
1. Select the cell for the formula
2. From the Function Library on the Formulas tab, select the More Functions  library, then Statistical
3. From the list of functions, select AVERAGEIF
4. The function’s dialog box opens.
a. Range-is the range of cells that you want evaluated by criteria. In the above example, the range of cells where the criteria is located is C3:C9
b. Criteria-is the criteria in the form of an expression, or text that defines which cells will be averaged. In the above example, the criteria is (including the quotation marks) “Manchester”
c.  Average_range- are the actual cells to be averaged if their corresponding cells in range match criteria. In the above example, the actual data is located in B3:B9.
AVERAGE IFS
Where Multiple Criteria is required to be met, use the AVERAGEIFS Function.
In the above example, to calculate the Average sales of all reps in Manchester with individual sales of £15,000 or over.
1. Select the cell for the formula
2. From the Function Library on the Formulas tab, select the More Functions  library, then Statistical.
3. From the list of functions, select SUMIFS
4. The function’s dialog box opens.
a. Sum_range- are the actual cells to add if their corresponding cells in range match all the criteria. In the above example, the actual data is located in B3:B9.
b. Criteria_Range1-is the range of cells that you want evaluated by the first criteria specified in Criteria1. In the above example, the range of cells where the criteria is located is C3:C9.
c. Criteria1-is the first criteria in the form of an expression, or text that defines which cells will be added. In the above example, the first criteria is (including the quotation marks) “Manchester”.
d. Repeat steps b & c for each additional criteria. Up to 127 criteria and criteria ranges are allowed.
COUNT /COUNTA
To count of a block of cells: Use Count to count numbers only and CountA (count all) to count numbers and text.
1. Select the cell for the formula
2. From the Function Library on the Formulas tab, select the More Functions library and then Statistical
3. From the list of functions, select Count or CountA.
4. The function’s dialog box opens.
a. Value1- the range of cells to count
b. Up to 255 ranges can be counted in Number2….Number255
?
COUNTIF
This function counts the figures in a given block of cells that meets specified criteria. E.g: Find the number of  sales figures of the Manchester Branch.
To insert the COUNTIF Function
1. Select the cell for the formula
2. From the Function Library on the Formulas tab, select the More Functions  library, then Statistical
3. From the list of functions, select COUNTIF
4. The function’s dialog box opens.
d. Range-is the range of cells that you want evaluated by criteria and counted. In the above example, the range of cells where the criteria is located is C3:C9
e. Criteria-is the criteria in the form of an expression, or text that defines which cells will be averaged. In the above example, the criteria is (including the quotation marks) “Manchester”
COUNTIFS
Where Multiple Criteria is required to be met, use the COUNTIFs Function.
In the above example, to count the number of sales of reps in Manchester with individual sales of £15,000 or over.
1. Select the cell for the formula
2. From the Function Library on the Formulas tab, select the More Functions  library, then Statistical.
3. From the list of functions, select COUNTIFS
4. The function’s dialog box opens.
a. Criteria_Range1-is the range of cells to be evaluated by the first criteria specified in Criteria1. In the above example, the range of cells where the criteria is located is C3:C9.
b. Criteria1-is the first criteria in the form of an expression, or text that defines which cells will be added. In the above example, the first criteria is (including the quotation marks) “Manchester”.
c. Repeat steps b & c for each additional criteria. Up to 127 criteria and criteria ranges are allowed.

Logical Functions
IF
This function is used when a decision is needed based on what is entered in a given cell.  In the above example, the Achieved Target column should show ‘Yes’ if a sales figure is over or equals to £15,000 an d ‘No’ if this criteria is not met.
1. Select the cell for the formula
2. From the Function Library on the Formulas tab, select the Logical library.
3. From the list of functions, select IF
4. The function’s dialog box opens.
a. Logical_test – a logical expression that results True or  False. In this example, the test is, is the sales figure of the first salesperson greater or equal to £15,000?
b. Value_if_true- if the result of the above expression is TRUE then what value should the IF function result in. In this example, the result will be the text YES.
c. Value_if_false- if the result of the above expression is FALSE then what value should the IF function result in. In this example, the result will be the text No.
It is possible to use formulas in the value if true /false arguments. In the above example, a commission is payable to each salesperson. If a target of £15,000 has been met or exceeded, then a 10% commission is paid.
The If Function would be entered as: