Ex - 05 SPREAD SHEET – Formulas and Functions
AIM:
To create a worksheet for the given data with various functions like Sum, Average, Count, Min,
Max &Logical functions [IF, AND].
Procedure:
A. Creating data for ten records
Open the Microsoft Excel 2007 and Enter the values in the table as shown in Figure 5.1.
Figure 5.1: Enter Values in Table
B. Font & Alignment & Color
I. Select the cells to format.
II. Choose Format from Cells group a pull down menu appears. Click Format Cells. Format Cells
windows appears.
III. Select the font face, font color and click OK as shown in Figure 5.2.
Figure 5.2: Format Cells
C. Using Formulas and Functions
Formulas are nothing but mathematical expression. An expression is an alternate sequence of
operand and operator(+, -, *, /, ^). The general form is
= cell address 1 + cell address 2 + …. cell address n
SUM Function
In Excel, the sum function allows us add values in a data specific cell values up to 255 cells.
Syntax: = SUM (Value1, value2,……., value 255) or =Sum(select range of cells).
Perform the following steps for finding SUM
(i) Select the cell where the computed total should appear.
(ii) Click Formulas tab Autosum select Sum
(iii) The range of cells to be added will be selected and the sum will be calculated.
(iv) Click Enter to get the sum as shown in Figure 5.3
a.
Figure 5.3: SUM Function
(v) Click and drag the J8 cell corner (+) pointer to apply the formula to the cells J9 to J12 as shown in
Figure 5.4.
Figure 5.4: SUM Function (Click & Drag)
AVERAGE Function
The average function returns the average of a selected data range or up to 255 cells. The average
function ignores text values, logical values, and empty cells.
Syntax: = AVERAGE (Value1, value2, ….., value 255) or = AVERAGE (select range of cells).
Generally, the average function adds each number and then divides the result by the number count.
Perform the following steps for finding AVERAGE for our data
(i) Select the cell where average should appear.
(ii) Enter the formula: =AVERAGE(D8,E8,F8,G8,H8,I8) as shown in the Figure 5.5 and press
Enter key.
(iii) Click and drag the K8 cell (+) mouse pointer to apply the formula to the cells K8 to K12 as
shown in Figure 5.6.
Figure 5.5: AVERAGE Function
Figure 5.6: AVERAGE Function (Click & Drag)
COUNT Function
The count function is used to count the number of cells containing numbers in a data range or up
to 255 cells. While using the count function arguments, numbers, date or text representation of the
numbers are counted.
Syntax: = COUNT (Value1, value2, ….., value 255) or = COUNT (select range of cells).
Perform the following steps for finding AVERAGE for our data
(i) Select the cell where count should appear.
(ii) Click Formulas tab in Autosum and select Count.
(iii) The cell range that has to be counted will be selected as shown in the Figure 5.6.
(iv) Click Enter, the result will be displayed as shown in Figure 5.7.
Figure 5.6: COUNT Function
Figure 5.7: COUNT Function
MAX Function
The MAX function returns the maximum (Highest) value in the data range or up to 255 cells. The
MAX function ignores text values, logical values, and empty cells.
Syntax: = MAX (Value1, value2, ….., value 255) or = MAX (select range of cells).
Perform the following steps for finding maximum value
(i) Select the cell where maximum value should appear.
(v) Click Formulas tab in Autosum and select MAX.
(vi) The cell range from where the maximum value is to be taken is selected as shown in the
Figure 5.8.
(ii) Click Enter, the result will be displayed as shown in Figure 5.9.
Figure 5.8: MAX Function
Figure 5.9: MAX Function
MIN Function
Follow the above steps to get minimum value as, click Formulas tab in Autosum and select MIN.
D. Finding the result using logical function
The steps given below are followed to find the result using logical functions.
i. To find the result of the first student, click the first cell K8 and enter the formula as.
=IF(AND(D8>=40,E8>=40,F8>=40,G8>=40,H8>=40,I8>=40), IF(J8/600*100>=75, "DISTINCTION",
IF(J8/600*100>=60, "FIRST", IF(J8/600*100>=40, "SECOND", "PASS"))), "FAIL")
The result will be displayed as shown in the Figure 5.10.
Figure 5.10: Formula (IF Condition)
(vi) Click and drag the K8 cell corner (+) pointer to apply the formula to the cells K9 to K12 as shown
in Figure 5.11.
Figure 5.11: Formula (IF Condition - Click & Drag)
Result:
A simple worksheet for the given data with various functions like Sum, Average, Count, Min,
Max &Logical functions [IF, AND] has been created successfully.
