Spreadsheets
Section3 More formulae and functions
3.1 Review
Exercise3.1
Open a new Excel workbook and enter the following data:
Don’t type
“Formula”!!!
Please use
“Function”!!
3.2 Using Average function
Exercise3.2
Add a new row that will show the average of each month.
Method3.2
1. Key in AVERAGE to cell A7 under TOTAL.
2. Move to cell B7.
3. Enter the formula =AVERAGE (B3:B5).
4. Press: Enter.
The result of the calculation is 679.
Replicating the formula
5. Move to the cell in which the formula that you want to copy is stored. In this case
B7.
6. Point the mouse at the bottom right of this cell until a thin black cross + appears.
7. Then holding down the left mouse, drag across cell D7 (where you want the
Page 1 of 10 21 Aug, 2025
Spreadsheets
formula copied to).
8. Release the mouse.
The spreadsheet now looks like this.
9. Call [Link] and show your sheet to her
3.3 Using the Max function
Info
The MAX and MIN function is used to find the maximum or minimum value in a cell
range.
Use MAX to find the maximum. The formula will be:
=MAX(B3:B5)
Use MIN to fine the minimum. The formula will be:
=MIN(B3:B5)
Excercise3.3
Under the row heading AVERAGE enter a new row heading BEST SALES. Using the MAX
function, create a formula that generates the maximum sales for Jan. Replicate the
formula for the other months.
Method3.3
1. Move to cell A8 and enter the heading BEST SALES.
2. In cell B8 enter the formula:
=MAX(B3:B5)
3. Press: Enter.
4. The result should be 976.
Page 2 of 10 21 Aug, 2025
Spreadsheets
5. Replicate the formula to the rest of the months. The result should be like this:
6. Call [Link] and show your sheet to her
3.4 Using the Min function
Excercise3.4
Under the row heading BEST SALES enter a new row heading WORST SALES. Using the
MIN function, create a formula that generates the minimum sales for Jan. Replicate the
formula for the other months.
Method3.4
1. Move to cell A9 and enter the heading WORST SALES.
2. In cell B9 enter the formula:
=MIN(B3:B5)
3. Press: Enter.
4. The result should be 155.
Page 3 of 10 21 Aug, 2025
Spreadsheets
5. Replicate the formula to the rest of the months. The result should be like this:
6. Call [Link] and show your sheet to her.
3.5 Use the IF function
Info
The IF function is used to test a specified condition and return a verdict. As an example,
in this spreadsheet you could determine if the TOTALS for each month are good or bad
and generate a result of ‘Good’ or ‘Bad’ in an appropriate cell. Let’s assume a total of
over 2000 is good. For Jan, in cell B10, the formula could be:
=If(B6>2000,”Good”,”Bad”)
=If ( Condition , “value if true” , “value if false” )
Result should be ‘Good’ since the TOTAL for Jan is 2037.
Use the following symbols with IF functions:
= Is equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
Excercise3.5
Under the row heading WORST SALES enter a new row heading STATUS. Use this row to
generate Good or Bad for each month using the IF function. Assume that over 2000 is
good.
Page 4 of 10 21 Aug, 2025
Spreadsheets
Method3.5
1. Move to cell A10 and enter the heading STATUS.
2. Move to cell B10 and enter the formula:
=IF(B6>2000,"Good","Bad")
3. Press: Enter.
4. The result is Good.
5. Replicate this formula for the other months.
Page 5 of 10 21 Aug, 2025
Spreadsheets
3.6 Using the Count function
Info
COUNT is a useful function. Use it to count the number of cells in a range. Use the
COUNT in a formula as follows:
=COUNT(B2:D2)
Numbers and dates are counted within a specified range. Any cells containing no entries
or text entries are not counted,
Excercise3.6
Under the row heading STATUS, leave on row blank and in the next row enter the
heading NO OF YEARS. In the cell directly below this heading, using the COUNT
function, enter a formula to count the number of years covered in the spreadsheet.
Method3.6
1. Move to cell A12 and enter the heading NO OF MONTHS.
2. Move to cell B12 and enter the formula:
=COUNT(B6:D6)
3. Press: Enter.
4. The result 3 is displayed.
3.7 Save your spreadsheet as Ex3 Office Sales in your own folder.
3.8 Close the files and exit Excel
Page 6 of 10 21 Aug, 2025
Spreadsheets
Practice3
Load Excel. On a new sheet enter the following data and follow the instruction bellow.
Note: In the ‘formula’ cells enter a formula for each month totals.
1. On cell B14 enter a formula to calculate the average of the scores.
2. On cell B15 enter a formula to show the highest score (use the MAX function).
3. On cell B16 enter a formula to show the lowest score (use the MIN function).
4. In the Status column, use the IF function to display PASS when the Score is
greater than 45 and FAIL when it is less than 45. Replicate the formula for all
students.
5. On cell B17 enter a formula to count the number of students (use the COUNT
function).
6. Save the spreadsheet as P3 Exam Results.
Page 7 of 10 21 Aug, 2025
Spreadsheets
Extra; Using the Round function
Info
The Round function is used to the nearest in a cell value.
Use ROUND to get to nearest. The formula will be:
ROUND (NUMBER, NUMBER DIGITS)
NUMBER DIGITS;
The number of digits which you want to round, Negative rounds to the left of
the decimal point.
Example;
ROUND (D7, 0) …… 532
ROUND (D7, 1) …… 532.3
ROUND (D7, 2) …… 532.33
ROUND (D7, 3) …… 532.333
ROUND (D7, 4) …… 532.3333
Page 8 of 10 21 Aug, 2025
Spreadsheets
Exercise3.10
Make the following sheet and add a new row that will show the nearest of each month.
Method3.10
1. Key in NEAREST to cell A8 under AVARAGE.
2. Move to cell B8.
3. Enter the formula =ROUND (B7, 0).
4. Press: Enter.
The result of the calculation is 666.
Replicating the formula
5. Move to the cell in which the formula that you want to copy is stored. In this case
B8.
6. Point the mouse at the bottom right of this cell until a thin black cross + appears.
Page 9 of 10 21 Aug, 2025
Spreadsheets
7. Then holding down the left mouse, drag across cell D8 (where you want the
formula copied to).
8. Release the mouse.
The spreadsheet now looks like this.
9. Call [Link] and show your sheet to her.
Page 10 of 10 21 Aug, 2025
