Developing e-learning content
© Copyright, 2013 by TAFE NSW - North Coast Institute
|Date last saved: 2 July 2013 by Amanda Walker |Version: 1.2 |# of Pages = 35 |
Copyright of this material is reserved to the Crown in the right of the State of New South Wales. Reproduction or transmittal in whole, or in part, other than in accordance with the provisions of the Copyright Act, is prohibited without written authority of TAFE NSW - North Coast Institute.
Disclaimer: In compiling the information contained within, and accessed through, this document ("Information") DET has used its best endeavours to ensure that the Information is correct and current at the time of publication but takes no responsibility for any error, omission or defect therein. To the extent permitted by law, DET and its employees, agents and consultants exclude all liability for any loss or damage (including indirect, special or consequential loss or damage) arising from the use of, or reliance on, the Information whether or not caused by any negligent act or omission. If any law prohibits the exclusion of such liability, DET limits its liability to the extent permitted by law, to the re-supply of the Information.
Third party sites/links disclaimer: This document may contain website contains links to third party sites. DET is not responsible for the condition or the content of those sites as they are not under DET's control. The link(s) are provided solely for your convenience and do not indicate, expressly or impliedly, any endorsement of the site(s) or the products or services provided there. You access those sites and use their products and services solely at your own risk.
Acknowledgements:
Cover page images
|Image |Attribution |
|[pic] |Image: 'Dunedin ICT Internship Speed Dating 2010' by Samuel Mann |
| | |
| |Licence: Under a Creative Commons Attribution 2.0 |
|[pic] |Image: 'What keeps us connected...' by cell105 |
| | |
| |Licence: Under a Creative Commons Attribution 2.0 |
|[pic] |Image: 'Servers' by JohnSeb |
| | |
| |Licence: ShareAlike 2.0 Generic (CC BY-SA 2.0) |
Table of Contents
Unit Notes 1
ICAICT203AOperate application software packages 1
Topic 3 – Spreadsheet Software 1
Topic 3 - Spreadsheets Software 5
Using the Unit Notes 5
Introduction 5
What is a Spreadsheet software package? 6
3.1 Select spreadsheet software appropriate to perform activity 6
Types of application packages 6
What is an integrated package? 7
Features of a spreadsheet package 7
Activity SP1.1: Selecting spreadsheet software 8
3.2 Identify document purpose and audience 8
Activity SP2.1: Document Audience and Purpose 9
3.3 Simple Formulas and Functions 9
Data types 9
Data entry 10
Understanding the main features of a spreadsheet 10
Use simple formula 11
Use operators 12
The order of operations 13
Use simple functions 13
Conditional Logic - IF 17
Conditional Summation 17
Lookup tables 18
Use absolute reference 21
Activity SP3.1: Formulas and Functions 22
3.4 Spreadsheet settings 24
Format text and numbers 24
Cell alignment 25
Page layout 25
Sort 26
Graphics 26
Creating charts 26
Built-in Help resources 30
Activity SP4.1: Spreadsheet settings 31
3.5 Naming and storing of documents in appropriate directories and printing 33
File Names 33
Storage 33
Versioning 33
Different file formats 33
Printing 34
Summary 34
Appendix 35
Topic 3 - Spreadsheets Software
These notes explore the use of spreadsheet software for business and mathematical applications. They will cover performing basic operations, including creating and formatting spreadsheet data, incorporating charts and objects, customising and printing spreadsheets. Also covered will be the revision of file management and naming conventions.
Using the Unit Notes
Icons and symbols are used throughout the guide to provide quick visual references.
They indicate the following:
|Icon |Meaning |Icon |Meaning |
|[pic] |ACTIVITY: An activity is listed to be | |ACTIVITY: A Learning activity requiring |
| |completed | |some physical action |
|[pic] |WWW: A web link is listed | |REFLECTION: A point is to be considered and|
| | | |thought about more deeply |
|[pic] |IMPORTANT: A pivotal point is detailed |[pic] |SEARCH: A particular item / book etc. needs|
| | | |to be found and applied |
Introduction
These notes will help you to develop your knowledge and skills to use spreadsheets to meet organisational needs. This will involve an understanding of:
• Data types and data entry
• Main features of spreadsheet worksheet
• Formatting text and numbers
• Using formulas, operators, and simple functions
• Using absolute reference
• Creating charts
• Design guidelines
While every effort has been made to keep the notes generic it has been important to use explanatory examples using a spreadsheet. For this purpose only Microsoft Excel has been used for examples. Most other spreadsheet applications are capable of performing all of these activities.
For detailed and up to the minute instructions on your spreadsheet application, visit the manufacturers website or any other many other tutorial websites on the internet.
What is a Spreadsheet software package?
Spreadsheets can be used anywhere there is a need to sort, organise, calculate, and redisplay data — so they are typically used for invoices, budgets, inventories and timesheets in an organisation. A spreadsheet can summarise information from many sources in one place and present the information in an easy-to-read way. It organises data, such as numeric values like dollars and cents, into columns and rows. A spreadsheet can also be used to perform calculations with the data, like adding up the numbers in a column to give a total.
3.1 Select spreadsheet software appropriate to perform activity
There are many spreadsheet software packages in the market place today. Common packages include Apple iWork, Microsoft Excel, and Open Office.
Which package you chose to use will depend on a few factors. The hardware and operating system platform on which you are working is a critical aspect. Are you running an Apple or Windows operating system? Are you looking for an integrated package or a standalone package? Another main deciding factor is price, while proprietary software is purchased; openware software is free to download from the internet.
Then, in terms of business efficiencies and of good practice, employees need to be able to competently and confidently use the most appropriate software to meet business needs. Your choice will not only depend on the ease of use (most effective use of time) but also what features each package contains to best meet the business needs.
Lastly, the compatibility with others should always be kept in mind – what will be the best software package for now and into the future. What is the most compatible package for our business and partners? If you share documents within your business and with your partners and customers – what packages are they using and will the package you choose be compatible with them?
Types of application packages
Software applications are available in a variety of types. Each business should consider which type of package best suits their requirements. The choices are divided into three broad categories:
1. proprietary software (sometimes referred to as ‘off the shelf’ software) which is sold through retail outlets
2. freeware/openware which is made available free of charge over the Internet
3. custom software (known also as ‘in-house’ software) which is written specifically for the business by either employees or contracted programmers
A spreadsheet is not commonly written as custom software as there are many packages already available on the market.
Within each of these categories applications can be available as ‘stand-alone’ programs or they can be part of an integrated package.
What is an integrated package?
An integrated package is a software package that contains a suite (several applications whose data is compatible). These packages usually include word processing, spreadsheet, presentation and database applications. The most well-known examples are:
• Apple iWork:
• Microsoft Office:
• Open Office:
Information on these packages can be found at the relevant website
Features of a spreadsheet package
Professional spreadsheet packages are capable of performing many intricate tasks. Most of these tasks are common across the packages. It is important to consider the most important features for your business and then select the application that best supports these needs. Here is a list of some of the features you would expect to find in most spreadsheet packages:
• Addition
• Conditional logic
• Conditional summation
• Division
• Lookup
• Multiplication
• Subtraction
• Summation
• Charts
• Font and formatting tools
• Graphics
• Objects
• Sorting
• Help
Activity SP1.1: Selecting spreadsheet software
You are working at a company called Pensline who manufacture and sell specialised corporate branded stationery including pens and paper supplies. To test and prove their products the company has always used pens and paper for customer orders and letters using pre-printed order books and letterhead paper. The need has arisen for Pensline to implement computer generated invoices, stock listings, stocktake results, sales and purchasing data.
The company is using Windows based personal computers with the latest version of the Windows Operating System loaded. The company shares documents with its business partner Paperline and supplier InkIcons who use Microsoft packages. They have recently started using the Microsoft Word package for their company documents.
You have been asked to investigate spreadsheet software packages on the market and recommend an appropriate package for Pensline to implement.
1. Using the internet or alternative source, research three common spreadsheet software packages. List the packages found and the company website.
2. Recommend a package that will be able to:
a. Create charts
b. Do summation, multiplication and division
c. Perform inbuilt functions to complete subtotals
d. Format numbers into currency and percentage
e. Format fonts, colours and borders
f. Be compatible with their operating system and other software
g. Be compatible with their partners and suppliers
3.2 Identify document purpose and audience
When creating and editing spreadsheet documents it is important to have an understanding of the purpose of the document so you can ensure its effectiveness. Is the purpose of the document to provide information like a list, record information, calculate percentages, analyse data, display trends and information in charts, place an order, send an invoice or statement, or something else?
It is vitally important to be able to answer this question for your document before you begin. The purpose of the document will dictate what type information needs to be included, the design, formatting and layout, and the security and storage of the document.
When you understand the purpose of the document you then need to think about who will be using this document – what is the audience? Is it an internal document only for staff? Is it for financial staff, office and administration staff? Do you need to apply security or protection to the formulas so they are not accidentally deleted? Is it for the management team of the business? Is it solely for data entry or analysis?
Again, this information will assist in the design of the document, the presentation, the security and complexity.
Even though you may have answered these questions and are confident that you know what the purpose and audience is, it is always a good idea to have a colleague or client confirm your thoughts. This way you can be sure your final spreadsheet has a good chance of achieving it’s desired purpose and the intended audience understands the content.
Activity SP2.1: Document Audience and Purpose
In the table below, write down a purpose and audience for each document type.
|Document Type |Purpose |Audience |
|Stock on hand spreadsheet | | |
|Invoice |e.g. will need to calculate the total invoice | |
| |price based on number of items purchased and unit | |
| |price per item | |
|Statement | | |
|Financial report | | |
|Team sport results | | |
3.3 Simple Formulas and Functions
Data types
Most spreadsheets can handle the following types of data:
• numbers
• dates
• times
• text
Numbers
Numbers can have different formats. On a computer, whole numbers are represented in a different way to decimals. In spreadsheets we often work with even more specialised numbers like time of day and money amounts. Spreadsheets are specifically designed to handle each of these different types of numbers appropriately.
Dates
Dates contain numbers and we commonly use different ways of representing them. For example:
• 5/9/2007
• 5-9-2007
• 5-Sep-2007
• September 5 2007
While we have little trouble with these variations, a computer program would. Most spreadsheet programs require you to use just a few basic alternatives. To add to the difficulty, USA dates are different to Australian date formats. In Australia 5/9/2004 means September 5th, while in the USA it means 9th of May.
Times
Times are also tricky. We can be using a 12-hour clock or a 24-hour clock. The spreadsheet needs to know which is being used; otherwise calculations based on time could be incorrect.
Text
Text is more straightforward and most spreadsheets will treat any combination of characters with numeric and non-numeric characters as text. For example, a telephone number like: 9123 456 is likely to be treated as text rather than a number because it contains a space.
Data entry
There are a number of methods of data entry. When creating a spreadsheet document from scratch you can start with a blank document and create your own spreadsheet from the keyboard. Alternatively, you can import data from an existing spreadsheet.
For more information and tutorials on how to do this, there are documents you can download from the internet. You should also use the built-in Help resources in your software to guide you through the steps in detail.
Understanding the main features of a spreadsheet
Here are the main features that a spreadsheet package may contain, in terms of the way in which the spreadsheet file is set up. You’ll need to investigate if your package works a little differently to the examples below for the Microsoft Excel spreadsheet package.
Worksheets
A worksheet is a single sheet in the spreadsheet used to list and analyse data. You can enter and edit data on several worksheets simultaneously and perform calculations based on data from multiple worksheets.
Workbook
In Microsoft Excel, a workbook is the file in which you work and store your data. Each workbook can contain many worksheets and you can organise various kinds of related information in a single file.
Sheet tabs
The names of the worksheets appear on tabs at the bottom of a workbook window. To move from sheet to sheet, click the sheet tabs.
Features of a worksheet
The screen shot below shows the main features of an open worksheet:
[pic]
Figure 1 Microsoft Excel Spreadsheet window
Use simple formula
Formulas calculate values in a specific order. Typically, you might want to add together all the values in a column or row of your spreadsheet. This is usually done by entering a formula that enables the addition (+) operation to be applied to a range of cells. Because the cells are each identified by a unique name (row number and column letter) it is easy to specify in the formula which cells are to have the calculation applied to them.
The description below is based on the spreadsheet package Microsoft Excel, but most spreadsheets use the same system for representing formulas and ranges of cells.
In most spreadsheet packages a formula always begins with an equal sign: =. The equal sign tells the spreadsheet program that the following characters make up a formula. Following the equal sign are the elements to be calculated (the cells containing the numbers for the calculation, or operands), which are separated by calculation operators (+, -, * multiply or / divide).
The spreadsheet program calculates the formula from left to right, according to a specific order for each operator in the formula. For example:
=B2+C2-D2
This entry will calculate the contents of cell B2 added to the contents of cell C2 from which the contents of cell D2 will be subtracted.
You can change the order of operations by using parentheses (round brackets). After the formula is typed into the cell, the calculation executes immediately, the numeric value of the answer is displayed in the cell, and the formula itself is visible in the formula bar.
In the example below, the parentheses around the first part of the formula force the spreadsheet program to calculate B4+B5 first and then divide the result C3:
=(B4+B5)/C3
The result of any formula will change when the contents of any of its cell references change. For example, if a value in any of the following cells changes, the result of the formula =B2+C2+D2 also changes.
Make sure you know how your spreadsheet package represents formulas and cell ranges and how it may be different to examples given above.
Use operators
Operators are actions that are applied to a value or values. Operators are usually represented by a symbol. For example, + (plus, or add) is an operator that is usually applied to two values: 3+4 produces a new value 7. You are probably familiar with the basic arithmetic operators.
Spreadsheets use the usual arithmetic operators but introduce several of their own to represent things like cell ranges. For example:
D5:D20
represents the cells from D5 to D20, as formulas can also refer to names of cells or to ranges (groups) of cells.
Other operators you’ll find within a spreadsheet are represented as:
|Operator |Description |
|% |percent |
|^ |Exponentiation (raised to the power of) |
|* |multiplication |
|/ |division |
|+ |addition |
|- |subtraction |
The order of operations
You may not be so familiar with the idea that when several operators appear in the same expression, for example:
4 + 3*5
The operations will have precedence. This means that the operators with highest precedence are performed first. In the example above, multiply (*) has a higher precedence that plus (+). So the value of 4+3*5 is 19 and not 35.
An easy rule to remember when evaluating arithmetic expressions, that is, how the order in which a calculation is performed, is BODMAS:
Brackets ()
Of of
Division /
Multiplication *
Addition +
Subtraction -
If you are in any doubt about how a formula you write will be calculated, put brackets around the parts you want performed first.
If you are not using Microsoft Excel, check the order in which operations are performed (operator precedence) in your spreadsheet package to check.
Use simple functions
Functions are predefined formulas that perform calculations by using specific values called arguments. For example, the SUM function adds values or ranges of cells.
Arguments can include numbers, text or cell references. The argument you designate must produce a valid value for that argument.
The structure of a function is as follows:
1. begins with the function name
2. followed by an opening parenthesis
3. then the arguments for the function separated by commas
4. then a closing parenthesis.
For example:
[pic]
Figure 2 Structure of a function
If the function starts a formula, type an equal sign (=) before the function name. As you create a formula that contains a function, the Formula Palette will assist you.
Most spreadsheets will have the following useful functions:
• SUM – adds the values of all of the cells in the range
• MAX – finds the largest number in the range of cells
• MIN – finds the smallest number in the range of cells
• COUNT – counts the total number of cells in the range
• AVERAGE – adds the values of each cell in the range and the divides by the number of cells in the range.
SUM
The most frequently used function is the SUM function, which is used to add the numbers in a range of cells. The syntax (the specific way of expressing a SUM) is:
SUM(number1,number2,etc)
number1,number2,etc can include the numbers for which you want the total value or sum. The items in the parentheses ( ) are arguments.
Numbers, logical values, and text representations of numbers that you type directly into the list of arguments are counted. See the first and second examples below.
Arguments that are error values, or text that cannot be translated into numbers, cause errors.
Examples
This formula equals 5
[pic]
Figure 3 Simple SUM
If the range of cells E1:E5 (cell E1 down to cell E5) contains the following numbers:
[pic]
Figure 4 Another SUM
SUM(E1:E3) equals 50
And
[pic]
Figure 5 SUM with cells references and numbers
SUM(E2:E5,15) equals 150.
MAX
This function returns the largest value (or number) in a set of values. The syntax used to express this is:
MAX(number1,number2,etc)
number1,number2,etc can include 1 to 30 arguments for which you want to find the maximum (highest) value.
Example
If the range of cells E1:E5 (cell E1 through cell E5) contains the following numbers, then:
[pic]
MAX(E1:E5) equals 27.
MIN
MIN returns the smallest value in a set of values.
Example
If E1:E5 contains the same numbers then:
[pic]
MIN(A1:A5) equals 2.
COUNT
This function counts the number of cells that contain numbers within the list of arguments. Use the function COUNT to get the number of entries in a number field in a range of numbers. The syntax for COUNT is:
COUNT(value1,value2,etc)
value1,value2,etc are the arguments that can contain or refer to a variety of different types of data, but only numbers are counted.
Arguments that are numbers, dates, or text representations of numbers are counted; arguments that are error values or text that cannot be translated into numbers are ignored.
Example
|[pic] |For this list: |
| |COUNT(A1:A7) equals 3 |
| |COUNT(A4:A7) equals 2. |
AVERAGE
AVERAGE returns the average of the group of numbers. The syntax used is:
AVERAGE(number1,number2,etc)
number1,number2,etc are the arguments for which you want the average. The arguments must be numbers or text that can be translated into numbers.
Example
If the range of cells E1:E5 contains the following numbers, then:
[pic]
AVERAGE(A1:A5) equals 11.
Conditional Logic - IF
In some instances you will want to analyse information based on set criteria. For instance, if a student receives a mark greater than 50, then they Pass, otherwise they will Fail. This situation requires the use of the function IF.
The IF function will returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.
The syntax for the IF function is:
IF(logical_test,value_if_true,value_if_false)
Example
IF(A10>50, “Pass”,”Fail”)
Logical_test is any value or expression that can be evaluated to TRUE or FALSE. For example, A10>50 is a logical expression; if the value in cell A10 is greater than 50, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE.
Value_if_true is the value that is returned if logical_test is TRUE. For example, in the above if A10 is 70 then the logical_test argument will return TRUE, then the IF function displays the text "Pass".
Value_if_false is the value that is returned if logical_test is FALSE. For example, in the above if the value in A10 is 40 then the logical_test argument will return FALSE, then the IF function displays the text "Fail".
Conditional Summation
Taking the IF function further, there is a function call SUMIF, that will complete a summation based on an IF function type criteria. As an example, only add up the sales for a particular salesperson.
The syntax for the SUMIF function is:
SUMIF(range, criteria, [sum range])
Example: Add up the sales for John
[pic]
Figure 6 SUMIF in Excel
Range is the range of cells to be evaluated. In this example the range is the names of the salespeople.
Criteria is the condition in the form of a number, expression, or text that defines which cells will be added. In this example the text to look for is John.
Sum_range are the actual cells to sum. If omitted, the cells in the range will be used. In our example, it will sum up the sales values.
The answer is 19.
Lookup tables
A lookup is a very powerful and useful feature of a spreadsheet — it can reduce keyboard entry time, thus reducing errors and saving time and effort.
A lookup table is used to find one piece of information that is based on another piece of information. A lookup table consists of a column or row of ascending values called compare values and corresponding data for each compare value.
In Microsoft Excel, two of the inbuilt lookup functions are:
• VLOOKUP
• HLOOKUP.
The value for the first column in VLOOKUP or the top row in the HLOOKUP can be text, numbers or logical values. Entries in the first column (or in the top row) must be placed in ascending order and are not case-sensitive. If the exact match cannot be found VLOOKUP or HLOOKUP returns the value for the largest that is less than or equal to it.
VLOOKUP function
The powerful VLOOKUP function (vertical lookup, which searches in columns) looks up a value in a table and returns a corresponding value. The VLOOKUP function has three arguments:
1. The value to lookup.
2. The range of cells containing the lookup table.
3. The column number within the table that contains the required result.
[pic]
Figure 7 Excel file with VLOOKUP example
Lookup_value: The value to be found in the first highlighted column or row of the table is usually a cell reference.
Table_array: The table of information where the data is looked up. It can be a range of cell addresses and absolute referencing or a named range.
Col_index_num: The column or row number in the table from which the result is to be obtained.
Naming a cell range
A name can be defined for a range of cells and then be used in a formula.
[pic]
Figure 8 Excel define name example
Define a Range Name in Excel
1. Highlight the required cell range.
2. Select Formulas tab and Defined Names group. Select Define Name.
3. Enter the desired name (no spaces are allowed for named ranges, so you should use underscore ( _ ).
4. Click OK.
Example of a name in a formula:
VLOOKUP(A5, PRODUCTS, 2)
Note: An absolute reference or table name should be used when specifying the table so that the addresses do not change if the function is copied to other cells on the spreadsheet.
The IF function
The IF function allows you to make a decision based on two possibilities. What if there are more possibilities? For example, in the case of student results recorded, and there are four possible outcomes for any student’s grade: A, B, P (Pass), or F (Fail). The best function to use in this case is one of the Lookup functions.
Here is more detail for the student grades example:
|Mark |Grade |
|Less than 50 |F |
|Greater than or equal 50 and less than 70 |P |
|Between 70 and 82 inclusive |B |
|83 or more |A |
We could try doing this with a ‘nested’ IF statement but it would become very complex and easy to make a mistake. It would look like this:
=IF(F4 ................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- step by step 1 using the sumif function
- step by step 1 open a non native file directly in excel
- developing e learning content
- introduction to excel formulae and functions
- advanced excel statistical functions formulae
- microsoft office excel 2013 warren hills regional school
- sumif and countif shivalik public school
- core ct home
- microsoft excel
- count and sum your data in excel 2002
Related searches
- e learning advantages and disadvantages
- advantages of e learning education
- learning content management system
- advantages of e learning pdf
- benefits of e learning education
- disadvantages of e learning pdf
- benefits of e learning article
- free e learning platforms
- e learning platforms for education
- navy e learning center
- best e learning platform
- why e learning is important