7.Using Functions common to Arithmetic Applications

Spreadsheet

It is a software application package which is used to organize, analyze and attractively present data such as budget or sales report.

It is a simple worksheet consisting of rows and columns in which any data can be entered.

Examples of Spreadsheet are:

A.Microsoft Excel

B. Lotus 1, 2, 3

Advantages of using Spreadsheet

User can quickly change, correct and update the data in spreadsheet.

Ability to quickly edit and format data

Ability to perform calculations

Ability to create graphs.

Launching Spreadsheet Application

Click on start from task bar

Point to All Programs

Point to Microsoft Office

Concepts and Terminologies

Columns are vertical blocks of cells in the Spreadsheet identified by letters.

Rows are the horizontal blocks of cells in the worksheet identified by numbers.

A cell is the space created by the intersection of a horizontal row and a vertical column.

It can contain a label (text), value (number) or formula.

Worksheet consist of cells formed by the intersection of rows and columns.

Workbook is similar to a book which contains a bind of several sheets of papers.

Types of Data

The information or data you enter in a cell can be text, numbers or formulas.

Text: is an entry which contains any combination of letters, numbers, spaces and any other special characters.

Number: is an entry which include only the digits zero (0) to nine (9) and any of the special characters; +, –, (), ., /, $, %, E, e. Number entries are used in calculations. [Currency, Fraction and Date]

Formula: this is an entry that begins with an equal sign (=). Formula entries perform calculations using numbers or data contained in other cells.

The resulting value is a Variable value because it can change if the data it depends on changes.

If a number entry does not begin with equal sign and does not change unless you change it directly by typing in another entry it is termed as Constant value.

Editing Entry

Change all the months to their full names. Example: JAN should be JANUARY, etc. To do this, use the following steps:

1.Click on the cell containing the data

2.Click in the formula bar and type or delete the data you want to change.

Copying and Moving Data

The contents of worksheet cells be moved by cutting them and then pasting them in a new location in the worksheet.

They can also be duplicated (copied) and pasted.

When you cut or copy cell contents, the contents are stored in the clipboard.

Then when they are pasted to the new location, the clipboard contents are copied into the selected cells.

Using the data in estimated expenses for Salary, Lease, Miscellaneous and Overhead to complete for February, March, April, May and June.

Apply copy and paste or move data symbol.

NOTE:

When pasting to the new location be careful because any existing entries will be replaced

Inserting Rows

Click on insert from the home ribbon

Click on Row to insert Rows.

Inserting Columns

Click on insert from the home ribbon

Click on Column to insert Columns.

Inserting Worksheets

Click on insert from the home ribbon

Click on Worksheet to insert a Worksheet

Renaming a Worksheet

Click on format from the home ribbon

Click on Worksheet to insert a Worksheet.

Rename a Worksheet

Right click on the sheet

Click on rename

Type the new name

Press enter key

Entering Formulas

A formula is an entry that performs a calculation.

The result of the calculation is displayed in the cell containing the formula

A formula always begins with an equal sign [=] which defines it as a numeric entry.

In entering formulas the following arithmetic operators are used

[+] for Addition, [–] for Subtraction, [/] for Division, [*] for Multiplication, [^] for Exponent

Excel performs calculation in a specific order of precedence. First Exponent, Multiplication, Division, Addition and Subtraction.

To override this order specify what should be done with parentheses.

Values on which a numeric formula performs a calculation are called Operands.

Relative and Absolute Reference

Relative Reference is a cell or range reference in a formula whose location is interpreted by Excel in relation to the position of the cell that contains the formula.

Absolute Reference is a cell or range reference in a formula whose location does not change when the formula is copied.

Changing Cell Alignment

Move to cell B3

Click on Format from the menu bar

Click on Cells

Open the Alignment tab

Select Right from the Horizontal textbox

Click on ok.

Previewing a Workbook

Click on preview from the quick access toolbar

Errors in Excel Calculations

Symbol

Meaning

####

The column is too narrow to display the result of the calculation. Example: A1 = 992499 and A2 = 552199 then = A1 * A2

#Name?

The formula contains a cell reference excel does not recognize. Example: = A1 + A2 + Aq

#Ref!

the formula refers to a cell that is not valid.

Example: = A1 + A2 + A3 and say one of the items is deleted.

#Value!

The formula refers to a cell that excel cannot use in a calculation.

Example: = A1 + A2 + A3 where cell A2 has a text.

#DIV/0!

A number is divided by zero.

Example: A1/0

Functions

Functions are pre-written formulas that perform certain types of calculations automatically.

Spreadsheet Application1.Launching Spreadsheet Application

2.Concepts and Terminologies

3.Features in the Spreadsheet Window

4.Types of Data and their uses

5.Creating and Saving Workbook

6.Constructing and Inserting Simple Formula

7.Using Functions common to Arithmetic Applications

SpreadsheetA.Microsoft Excel

B. Lotus 1, 2, 3

Advantages of using SpreadsheetLaunching Spreadsheet ApplicationConcepts and TerminologiesColumnsare vertical blocks of cells in the Spreadsheet identified by letters.Rowsare the horizontal blocks of cells in the worksheet identified by numbers.cellis the space created by the intersection of a horizontal row and a vertical column.Worksheetconsist of cells formed by the intersection of rows and columns.Workbookis similar to a book which contains a bind of several sheets of papers.Types of DataText: is an entry which contains any combination of letters, numbers, spaces and any other special characters.Number: is an entry which include only the digits zero (0) to nine (9) and any of the special characters;+,–,(),.,/,$,%,E,e. Number entries are used in calculations. [Currency, Fraction and Date]Formula: this is an entry that begins with an equal sign (=). Formula entries perform calculations using numbers or data contained in other cells.Variablevalue because it can change if the data it depends on changes.Constantvalue.Editing EntryJANshould beJANUARY, etc. To do this, use the following steps:1.Click on the cell containing the data

2.Click in the formula bar and type or delete the data you want to change.

Copying and Moving DataNOTE:When pasting to the new location be careful because any existing entries will be replaced

Inserting RowsInserting ColumnsInserting WorksheetsRenaming a WorksheetRename a WorksheetEntering Formulas=] which defines it as a numeric entry.+] for Addition, [–] for Subtraction, [/] for Division, [*] for Multiplication, [^] for ExponentOperands.Relative and Absolute ReferenceRelative Referenceis a cell or range reference in a formula whose location is interpreted by Excel in relation to the position of the cell that contains the formula.Absolute Referenceis a cell or range reference in a formula whose location does not change when the formula is copied.Changing Cell AlignmentB3Formatfrom the menu barCellsRightfrom the Horizontal textboxok.Previewing a WorkbookErrors in Excel CalculationsSymbolMeaningExample: = A1 + A2 + A3 and say one of the items is deleted.

Example: = A1 + A2 + A3 where cell A2 has a text.

Example: A1/0

Functions## Attachments2