Formulas & Functions

• Computing
• Higher Education (non-degree)

Formulas & Functions

Learning Outcomes/Aims & Objectives

• apply correct structure to formula containing multiple operators
  
• understand the importance of absolute & mixed references
  
• apply basic functions and IF functions
  
• apply learning to different scenarios
  

RECAP ON PREVIOUS LESSON

Click HERE to complete the Quiz

You will see the correct answers once you submit

TOPICS FOR THIS LESSON

Basic Formula

• Multiple Operators
• Absolute Reference

Basic Functions

• SUM
• AVERAGE
• MAX / MIN
• COUNT

More Functions - IF

 

DEMO OF MULTIPLE OPERATORS

Watch this short video

DEMO OF MULTIPLE OPERATORS

Further Videos:

https://youtu.be/J-W3thqtDpQ

Further Reading:

EXERCISE ON MULTIPLE OPERATORS

Download the workbook named BUSINESS CASE COSTINGS V2 

Go to the following sheet:

MULTIPLE OPERATORS

Enter formula into the cells highlighted in yellow to complete the tables, then use your completed work to answer the following questions:

What was Janes Standard Wages Bill?

1. £200
2. £4100
3. £5300
4. none of these

What was Kellys Gross Wages Bill

1. £3410
2. £5300
3. £3860
4. none of these

What % of budget was left in the IT department?

1. 10%
2. 0%
3. 44%
4. none of these

DEMO OF ABSOLUTE REFERENCES

Watch this short video

DEMO OF ABSOLUTE REFERENCES

Further Videos:

https://youtu.be/Ng0w0BHUgeo

https://youtu.be/u-oAyOUOL0U

Further Reading:

https://www.excel-easy.com/functions/cell-references.html

https://www.linkedin.com/learning/excel-essential-training-microsoft-365-17231101/copying-a-formula-into-adjacent-cells

EXERCISE ON ABSOLUTE REFERENCES

Open the workbook named BUSINESS CASE COSTINGS V2

Go to the following sheet:

ABSOLUTE REFS

Enter formula into the cells highlighted in yellow to complete the tables, then use your completed work to answer the following questions:

What was Janes Standard Wages Bill?

1. £2500
2. £2250
3. £2750
4. none of these

What was total costs including vat for Transport Vehicles

1. £3960
2. £3300
3. £1344
4. none of these

DEMO OF BASIC FUNCTIONS

Watch this short video

DEMO OF BASIC FUNCTIONS

Further Videos:

https://youtu.be/DUoz9v2CHjo

https://youtu.be/gqlXj_NFcMc

Further Reading:

https://www.excel-easy.com/functions/count-sum-functions.html

https://www.excel-easy.com/examples/sum.html

EXERCISE ON BASIC FUNCTIONS

Open the workbook named BUSINESS CASE COSTINGS V2

Go to the following sheet:

FUNCTIONS

Enter formula into the cells highlighted in yellow to complete the tables, then use your completed work to answer the following questions:

What was the total amount invested?

1. £23,498
2. £211,486
3. £186,411
4. none of these

How many codes were missing?

1. 0
2. 2
3. 4
4. none of these

what was the average number of days spent on projects?

1. 596
2. 107.5
3. 74.5
4. none of these

DEMO OF IF FUNCTION

Watch this short video

DEMO OF IF FUNCTION

Further Videos:

https://youtu.be/Zs9NuYw-F7k

Further Reading:

https://www.excel-easy.com/examples/if.html

https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2

EXERCISE ON IF FUNCTION

Open the workbook named BUSINESS CASE COSTINGS V2

Go to the following sheet:

BASIC IF

Enter formula into the cells highlighted in yellow to complete the tables, then use your completed work to answer the following questions:

Who is eligible for the Company Scheme?

1. Fred
2. Jill
3. Jordan
4. Joanne

Who is NOT eligible for a pay rise?

1. Anna
2. Fred
3. Kerry
4. Harry

How many projects were late?

1. 3
2. 2
3. 1
4. 0

CONSOLIDATION EXERCISE

Please review any incorrect answers

RECAP

• apply correct structure to formula containing multiple operators
  
• understand the importance of absolute & mixed references
  
• apply basic functions and IF functions
  
• apply learning to different scenarios

RECAP

Answers to the various exercises can be found in the file named BUSINESS CASE COSTINGS V2 ANSWERS