Module 3.1
Task 7 - Advanced Spreadsheet Functions


A.M.D.G.

Microsoft Excel comes with literally hundreds of built in functions that perform some simple or complicated calculations on data in a spreadsheet.

There are different functions for different purposes. One of the most commonly used function is the IF functions that can output different results depending on the result of a decision.

Conditional Formulae

A conditional formulae is one that uses IF to determine what it should calculate. You're unlikely to be asked to write a complete formula in the exam but you should have a good grasp of how they work.

For example:

Construct a spreadsheet for a company whose salespersons will only get a bonus if they make £6000 of sales.
A
B
C
1
 Name Sales Made Bonus Paid
2
 J Stewart £6500.00 £100
3
 B Kemp £4700.00 £0
4
 L Nawas £6389.00 £100

The formula in C2 is = IF(B2 > 6000,100,0) which means if B2 is greater than 6000 then value in C2 is 100 otherwise the value in C2 is 0.

  • Enter the following information in a new spreadsheet document
 
A
B
C
1
 Name Exam Result Pass or Fail
2
 Natalie 67  
3
 Lisa 72  
4
 Susan 48  
5
 Isobel 50  
6
 Lesley 51  
  • Enter a conditional formula in cell C2 that enters the word pass or fail if the result is more or less than 50%
  • Fill down this formula to cells C3 to C6
  • Now try and change the formula so that the following grades are inserted :
    • 70 - 100 = A
    • 60 - 70 = B
    • 50 - 60 = C
    • less than 50 = F
  • You may need to use several IF statements

Answer the following questions in your jotter.

  1. What are the nine categories of functions available in Excel?
  2. Choose three functions and describe how they are used.