FIT1013 – Week 1: Calculating Data
with Formulas and Functions
Learning Outcomes:
• Understand the principles of spreadsheets
• Utilise the basic Excel formulas to analyse data
• Understand Error values in Excel
• Perform what-if analysis to further analyse data
• Understand the differences between values and cell
references
2Tasks
 Introduction to unit – 30 minutes
 Weekly Objectives – 5 minutes
 The basic concepts of workbook, and principles of spreadsheets – 15
minutes
 Basic Excel formulas to analyse data – 15 minutes
– Explore application help on Functions (i.e. F1, Help tab, Insert Function)
– Working with DATE functions
– Working with COUNT functions
– Working with financial functions (e.g. PMT)
 Error values in Excel – 10 minutes
 What-if analysis – Goal Seek – 15 minutes
 Excel cell references – 15 minutes
 Post-class activities
31. Introduction to unit
 Staff details
 Workshops
 Applied Sessions
 Consultations
 Resources
 Assessments
4FIT1013: why is it useful?
 Excel knowledge assumed in many BIS core and elective units.
 Expertise in spreadsheet modelling and programming is prescribed by
IBL sponsors as essential knowledge for students on placement
 Spreadsheet modelling is the most widely used tool for Business
modelling
Excel & Access:
Useful business tools for
 Storing and manipulation of data
 Data analysis and visualization
 Decision Support
 Problem solving
 Presentation/reporting
5Excel 2019 and Access 2019
 Excel – the most widely used DSS software.
 Access/Excel + VBA enables
– Database
 from an Excel tables and list/s
 or from an Access database
 (other databases….)
– Data Analysis
 using pre existing analysis tools – e.g. Scenario Manager, Charts, data
tables, pivot tables, Excel functions, Goal seek, Solver
 Using user defined functions
 Using VBA
– Interface and automation
 built using VBA
63. The basic concepts of workbook, and principles of
spreadsheets
7Activity
What are the benefits of documenting contents of a workbook?
True/False:
Every workbook should be accessible to its intended users.
You should never use jargon or unusual terms in a workbook
84. Basic Excel formulas to analyse data
 Quick way to calculate summary data
 Every function follows a set of rules (syntax) that specifies how the
function should be written
 General syntax of all Excel functions:
– FUNCTION(argument 1, argument2, …)
 Square brackets indicate optional arguments:
– FUNCTION(argument1, [argument2=value2, …])
 An argument can be any type of value including text, numbers, cell
references, or even other formulas or functions
 Functions can be placed inside another function, or nested; nested
functions must include all parentheses
9Some Excel Functions
10
Activities
 Explore application help on Functions (i.e. F1, Help tab, Insert
Function)
 Working with DATE functions
 Working with COUNT functions
 Working with financial functions (i.e. PMT)
11
5. Error values in Excel
12
Activities
 Explore different types of error values in Excel.
 How to resolve them?
13
6. What-if analysis – Goal Seek
14
Activities
 How many acres of corn crops needed if your projected
market revenue is $100,000?
15
7. Excel cell references
 Workbooks can include data entered in cells that are then referenced
in formulas to perform calculations on that data.
 Types of cell references
– Relative References
 When a formula includes a cell reference, Excel interprets it as being located
relative to the position of the current cell.
– Absolute
 A fixed reference – one that always references the same cell no matter where it is
moved – is called an absolute reference.
– Mixed
 A mixed reference contains both relative and absolute references
16
Activities
 Explore each of type of references.
17
8. Post-class Activities
 Try these functions:
– Min(), max(), average(), sum(),…..etc
18
Reference
 Carey, P. (2019). New Perspectives Microsoft Office 365
and Excel 2019 Comprehensive. Cengage.