computer science unit 3

Types of Data

- Labels - Formulas - Values - Dates and time

Building Blocks of a Spreadsheet

- Row - Column - Cell

Export data

Similarly, you can export data from a spreadsheet to a text file or from a spreadsheet to a database.

Average

With the Average function, you can get the average of two or more numbers. It ignores any text input. In OpenOffice Calc, you can select up to 30 numbers to find the average of a set of numbers.

Spreadsheet software

the computerized version of paper-based accounting worksheets that were used to record data in earlier times.

Workbooks with the ______extension contain automated steps for performing repetitive tasks

xlsm

Errors Related to Formulas and Functions

#NAME: The #NAME error occurs if you enter an invalid cell reference in a formula. This is either due to the cell reference may not exist or it may not contain any values to carry out the operation. #VALUE: The #VALUE error occurs if you accidentally insert text into cells or formulas that accept or use only numeric data. This leads to calculation errors, and the program shows it as a "value" error. #REF: The #REF error occurs when you type in a cell reference that doesn't exist; or you deleted without updating the reference. You can eliminate this error if you add values in the blank or deleted cell reference(s) mentioned in the formula. #DIV/0!: In division operations, a situation may occur where the program makes an attempt to divide a number by zero (0). The #DIV/0! error can occur in two situations. One situation is when the program made an attempt to divide a variable by zero. Another situation is when the cell reference in the formula did not contain any data. #####: This error can occur for various reasons, For example, when you enter a value that's too lengthy to fit in the cell; or when a formula produces an output that's too large to display in the spreadsheet. The error can also occur when dates and times contain negative numbers.

Spreadsheet programs offer a variety of functions. Some of the commonly used functions are as follows:

- AVERAGE: This function returns the average of a given set of numbers or range of cells. Write this function as - AVERAGE(n1,n2,...n) or AVERAGE(address range). - MAX: This function returns the maximum value of a given set of numbers or range of cells. Write this function as MAX(n1,n2,...n) or MAX(address range). - MIN: This function returns the minimum value of a given set of numbers or range of cells. The syntax is similar to the MAX function. - SQRT: This function returns the square root of a given number. Write this function as SQRT(number) or SQRT(address). - ROUND: This function rounds a number to the nearest decimal. It takes a numeric value as the argument and an integer as the precedence. For example, ROUND(3.24, 1) returns 3.2, ROUND(3.24) returns 3, and ROUND(3.2, -1) returns 10. - COUNT: This function counts the numbers in a list of arguments or a cell range. For example, COUNT(1,1,4,2,eleven,b) returns 4. - NOW: This function returns the current time (for example, 03:29:14 PM). This function does not have any arguments. Write it as NOW(). - TODAY: This function returns the current date (for example, 02/17/15). Write it as TODAY().You can also use multiple functions in the same formula. For example, =SUM(A1:A10)-MAX(A1:A10) is a valid formula with multiple functions.

how to create a chart in OpenOffice Calc.

- Click Chart from the Insert menu. Choose the required chart type. - Then, choose the Data Range you want to present in the chart. You can use a series of rows or columns. After setting the data range, click Next. - You can use various options to tune the selected data. The Data Series option enables you to add more details to the spreadsheet data. - You can give an appropriate title to your chart under the Chart Elements option. If required, add a subtitle. - You can select the axis (X or Y) on which to display the data. Chart Elements also gives you the option to show grids on either axis. - You can change the chart's elements, layout, and styles from the spreadsheet's Format menu. - Finally, you can choose a legend, header, or footer. If you use a legend, you can select where it should appear.

Create a cell style: You can create a new cell style in one of the following ways:

- From the options in the Styles and Formatting window, right-click a style you want to link to and select New. - Right-click the blank white area of the Style and Formatting window and select New. - Select the object whose properties you wish to copy. Click the New Style from Selection icon at the top of the Styles and Formatting window. - Select a cell and drag it to the Styles and Formatting window.

Apply a cell style: You can apply cell styles in one of the following ways:

- Highlight the cell or group of cells you want to style. Open the Styles and Formatting window. Double-click the Cell Style name of your choice. - use the Fill Format mode to apply a similar style to cells in different sections. - Use the Apply Style list to style the cells of your spreadsheet. Click the down arrow at the top right of the formatting toolbar. Then, select Visible Buttons and choose Apply Style. - Assign styles to shortcut keys by selecting Keyboard in the Customize option of the Tools menu.

Worksheet Management

- Inserting or deleting a worksheet: To insert a worksheet, right-click the worksheet tab and then select the Insert option. To delete a worksheet, right-click the worksheet tab and then select the Delete option. - Renaming a worksheet: To rename a worksheet, double-click the tab title of the worksheet, and type in the new name. - Copying or moving data between worksheets: To copy or move data between worksheets, select the source cell or groups of cells, and right-click and select the Copy or Move option from the drop-down menu. Next, select the target worksheet, right-click the target cell or cells, and select the Paste option from the drop-down menu.

Here are the benefits of sharing a workbook.

- Multiple people can work on the same file simultaneously. This helps enhance the overall productivity, and saves time. - Multiple people can edit and make changes in the workbook. This helps build transparency because everyone is aware of changes to the workbook. - Multiple people can track changes to the workbook. This helps complete tasks on time.

various options or tabs of the Format Cells dialog box.

- Numbers: This tab enables you to select the currency, decimal, date and time, and fractions format, depending on the type of data you entered. - Alignment: This tab lets you align or adjust a cell's data vertically, horizontally, or centered. You can also wrap text, shrink text, or merge more than one cell. - Font: This tab enables you to change the font style and size of the data you enter in a cell. - Font Effects: This tab enables you to apply formatting such as strike through or underlining to the text. - Borders: This tab lets you choose a line style and color for the cell border. - Background: This tab lets you color the background for a cell. - Cell Protection: This tab enables you to lock cells or hide formulas. You should first protect the entire worksheet and then protect the sheet. A password is optional.

To customize the ribbon tabs:

- Select the File menu. - Click Options. - From the Excel Options dialog box that opens, click Customize Ribbon.

The following steps help you perform editing actions on a spreadsheet:

- To copy the data, right-click and select the Copy option from the drop-down menu. Alternatively, press the Ctrl and C keys on your keyboard. - To move the data, right-click and select the Cut option from the drop-down menu. Alternatively, press the Ctrl and X keys on your keyboard. - To paste the data in another location (cell or cells), right-click the cells and select the Paste option from the drop-down menu. Alternatively, press the Ctrl and V keys on your keyboard. - to delete data from the cells, right-click the cell (or group of cells) and select the Delete option from the drop-down menu. You can also delete the data by hitting the Delete key on the keyboard.

You can customize a spreadsheet by

- making changes to its tabs and group settings, - importing and exporting data, and - manipulating its properties.

Saving a Workbook (used to store Microsoft Excel workbooks)

- xlsx: Use xlsx to save a workbook in an open file format, which is the preferred format to transfer data between any applications. - xlsm: Use xlsm to save workbooks that contain embedded macros to automate simple repetitive tasks. - xlsb: Use xlsb to save very large workbooks in the binary format. This format makes the files smaller and occupy less storage space. - xltx: Use xltx to create Excel templates with common formatting. You can create multiple spreadsheets with the common formatting styles of one template.

The typical components of a formula include the following:

-standard prefix (the "equal to" sign) - parentheses (opening and closing) - operators (+, -, /, and *) - cell references or values called arguments

Let's suppose you want to import a text file into a spreadsheet

1. Click the Data tab on the Get External Data group, and select From Text. 2. On the Import Text File window, browse and find the file you want to import into the spreadsheet. Select the file and click Import. 3. In Step 1 of the Text Import Wizard, specify the file type (Delimited or Fixed Width), depending on your data type. Click Next. 4. In Step 2, use the Delimiters option to specify the delimiter that separates your columns. Specify the Text Qualifier options. Click Next. 5. In Step 3, specify the data format for each individual column. 6. Click Finish. 7. Select the cell where you want the data to go, and click OK.

To add or change spreadsheet properties:

1. Click the File menu. This opens the backstage view. 2. On the menu, select the Info option. 3. In the right panel, click Properties to open a drop-down menu with the Show Document Panel and Advanced Properties options. 4. Select Show Document Panel from the drop-down options. This will take you back to the spreadsheet. The document properties appear just below the ribbon. 5. Enter information such as author's name, title, subject, or a list of keywords. 6. Click the Close button.

To export spreadsheet data to a text file:

1. Click the Save As option on the File menu. 2. On the Save As dialog box, choose a compatible text file format for the worksheet. For example, you can choose Text (Tab delimited) or CSV (Comma delimited). 3. Browse to the location where you want to store this text file and then click Save. 4. A message box reminds you that your worksheet may contain features that your text file formats do not support. 5. If you still want to save the worksheet data into the new text file, click Yes.

To export spreadsheet data to a database:

1. Select the spreadsheet rows you want to copy. 2. Click the Datasheet View option. This will open the database file. 3. Paste the copied cells into it.

Spreadsheets allow three types of cell addressing. They include ________addresses, such as $E$1___________addresses, such as R$3, and __________mixed relative absolute addresses, such asD4.

1. absolute 2. mixed 3. relative

Before Jamie prints his spreadsheet, he should change the view to ____________ . Page layout page margin page customization page break .He can use ____________ to reduce the size of the worksheet to fit within the set number of pages.

1. page layout 2. page scaling

Let's use OpenOffice Calc to learn how to format cells in a spreadsheet. To format cells:

1.Right-click a cell or group of cells that you want to format. 2.From the drop-down menu, select Format Cells. 3. Select the options available in the dialog box that opens. 4.Click OK.

In OpenOffice Calc, up to how many numbers can you use to find the average of a set of numbers?

30

Viewing Options

:- Normal view: This view displays the data in normal view, which is a spreadsheet's default view. - Page Layout view: This view shows individual pages, along with their margins, headers, and footers. You can use the Page Layout view to add a header and footer and to change the margins and column widths - Page Break Preview view: This view shows the spreadsheet's page breaks in blue. You can adjust the page breaks by clicking and dragging the blue lines to the required space, if necessary - Full Screen view: This view shows only the worksheet. None of the other elements, such as the tool bar and status bar will be visible. To access this view, select Full Screen on the View tab. - Side-by-side view: you can view two separate workbooks side by side. This view helps when you want to compare data between two worksheets in the same workbook or in different workbooks. - Split worksheet: lets you split the worksheet window into two or four panes simultaneously. - New window: This view enables you to see a new window of the same workbook you are working on. To view multiple worksheets at the same time, click the New Window option on the View tab. Then, click Arrange All to select the desired view. - Freeze/Unfreeze pane: This option lets you view row and column headings as you work with a huge database. To freeze data in your spreadsheet, select the cell or group of cells you want to freeze. Then, click Freeze Panes on the View tab. - Zoom in and zoom out: This view enables you to see specific parts of the worksheet with the Zoom In option. To zoom in on any part, you can select one of three options.

What is wrong with the formula below? SUM(A1:A5)/SUM(B1:B5)

A formula or function should begin with =.

Values

A value in a spreadsheet includes any number that you enter in a cell, or a number that is derived from calculations.

What naming scheme identifies the columns of a worksheet?

A, B, C

What cell address indicates the intersection of the first row and the first column in a worksheet?

A1

cell address example

For example, when column C intersects row number 5, the cell address is C5. The first letter C represents the column number, and 5 represents the row number.

Formatting Rows and Columns

Formatting rows and columns in a spreadsheet is similar to formatting a cell. The main difference is that it applies to one or more rows or columns at once. You can insert and delete rows, and hide or show them. You can also adjust the height and width of the rows and columns. Let's look at the various formatting options for the rows and columns of a spreadsheet in OpenOffice Calc. Insert and delete: You can insert rows and columns individually or in groups. Use the Rows and Columns options on the Insert menu. Or, select the row or column where you want new rows or columns to appear, right-click, and select Insert Rows or Insert Columns. Hide or show: You can hide or show rows and columns in a spreadsheet with the Hide or Show options on the Format menu. Adjust height or width: You can adjust the height of rows and the width of columns in a spreadsheet from the Format menu. For example, to adjust the width of a column, first select the column on the Format menu. Then, click Width. Enter the width of the column. Or, right-click the column, click Column Width from the drop-down menu, and enter the width value for the column.

Labels

Labels describe data in a spreadsheet. You can use them to name a row or column that represents data.

Median:

Median: The median value is the middle number in a sorted set of numbers.

Rick needs to find the lowest number in a set of numbers that includes decimals. Which statistical function in a spreadsheet will help him do so?

Min

Page scale

Page scaling mode helps you increase or reduce the size of the worksheet to fit within the set number of pages you want to print. You'll find these options in the Scale to Fit group on the Page Layout tab.

date and time

Spreadsheets store the date and time in the form of numeric data. You can format this data to display the actual date and time. You can use this data for date-specific or time-related calculations.

ere's how you can calculate the average of numbers in OpenOffice Calc:

Syntax: AVERAGE (number1; number2; number3...number30) Example: AVERAGE (2; 6; 4). The result is 4 because the average of the three numbers is 4.

Edit a workbook

The Edit option allows everyone in a group to edit the contents of the workbook, update information, and track changes in the workbook.

Mode

The Mode function expresses the most frequently occurring number in a given set.

Protect a workbook:

The Protect option enables you to retain the record of all the changes to the workbook. You can take steps so that nobody can delete the change history of the workbook.

row

The horizontal divisions are rows. Each row is identified by a number. For example, the first row is numbered 1, and it is followed by 2, 3, and so on.

column

The vertical divisions are columns. Each column is identified by a letter. For example, the first column is A, and it is followed by B, C, and so on.

Changing the order of worksheets:

To move a worksheet before or after another worksheet within a workbook, right-click the title of the worksheet, then select the Move or Copy option from the drop-down menu. Next, select the target worksheet—the one in front of which you want to move or copy the worksheet. Alternatively, you can click the worksheet tab and drag it to the new position.

Navigating between worksheets

To move between worksheets, select the respective worksheet tabs visible at the bottom of the spreadsheet. Alternatively, use the Ctrl and Page Down or Page Up arrow keys to navigate between worksheets.

Sharing a Workbook

To share a workbook, place it in a shared folder on a specific network location and select the option that enables multiple users to access the workbook.

collection of two or more worksheets

Workbook

Page header and footer:

You can add a page header and footer at the top and bottom of the pages in your spreadsheet. The header or footer can include the workbook name, page number, and date.

Page margin:

You can adjust the space between the content and the edge of the page. You can select options such as Normal, Wide, and Narrow.

Auto format

You can apply a set of cell formats to a worksheet or a selected range of cells with a single click. Select the Auto Format option from the Format menu.

Page orientation

You can choose from two orientation options: Portrait and Landscape. Most spreadsheet records use a portrait page orientation by default

Import data:

You can import data from a text document, the web, a database, or other sources.

Paper size:

You can manually adjust the size of your spreadsheet. To select a different paper size, click Size on the Page Layout tab and select your desired paper size

Protecting worksheets

You can protect the entire worksheet or an entire workbook. For example, to protect a worksheet in Open Office, you need to select Protect Document from the tool menu, select the worksheet option, provide a password, and confirm it.

Creating and Applying Cell Styles

You can use cell styles to apply several formats to the spreadsheet cells at the same time. The spreadsheet will apply the same set of formatting to all data to which you've defined a cell style.

Rulers

You can use horizontal or vertical rulers to determine the width or height of data or cells in the spreadsheet.

Formatting Cells

You can use predefined cell formats built into the spreadsheet to format individual cells and their data.

__________ addresses do not change if you copy them to a different cell. _____________ addresses change depending on the cells you copy them to.

absolute and relevant

Data

any information that a spreadsheet program contains

A cell is identified by a

cell address

This cell is marked by a highlighted rectangular border known as the

cell pointer

The viewing options enable you to

change the way a worksheet looks on the screen. When you work with large amounts of data, these spreadsheet views make it easier for you to focus on different tasks

Which type of chart or graph would be appropriate to display the annual petrol consumption of your father's car?

column chart bar graph

To replace this default template and work on a template of your choice, you need to...

customize the spreadsheet formats.

A computerized worksheet stores

data in a tabular form and allows you to change and correct data. You can also do calculations with various formulas. Each time you make changes to the data, the related calculations automatically reflect the new values.

Working professionals and organizations use spreadsheets to

do day-to-day calculations and to store large-scale data such as budget and expenditure, finance, and mathematical calculations.

Which sign or symbol will you use to lock cells for absolute cell reference?

dollar sign

Businesses use spreadsheets for

financial accounting. Typically, they use the spreadsheet to calculate balance sheets, profit and loss data, and to store checkbooks digitally.

Which option aligns the contents of a cell vertically and horizontally?

format cells

Stdev (standard deviation):

function helps you see how far a number varies, on average, from the average value of the list.

Statistical Functions

functions that enable statistical analysis of data.

The________function will help find the most frequently occurring number from a set of numbers.

mode

Formulas

perform calculations in a spreadsheet. To enter a formula, you need to first enter the = sign and type the formula.

Which graphical element of a spreadsheet does this image represent?

pie chart

Educational institutions use spreadsheets to

record student grades in specific subjects, as well as students' overall scores. Institutions use this data to analyze the performance of students.

Which type of graph is useful to record scientific experiments that require the analysis of varying or multiple changes?

scatter plot

steps To perform calculations,

select a cell and type the = (equal to) sign first. Next, type in the required numbers and operators. Spreadsheets follow the same operator order of precedence that traditional mathematics uses. The order of parentheses, exponents, division, multiplication, addition, and subtraction. Spreadsheets perform all calculations from left to right.

Cell formatting helps ensure that

the data in a cell has consistent formatting.

Min (minimum)

the least amount

cell

the smallest unit of a spreadsheet, and it is formed by the intersection of a row and a column. You can enter data into an active cell.

Individuals use spreadsheets to

track daily expenses and to calculate monthly expenses

An absolute address has a $ sign before both the row and the column values

true

icrosoft Excel offers several viewing options, such as

workbook views, zoom controls, window options, and options to show or hide worksheet elements.

Rank

you can find a number's rank or position from a disorganized set of numbers.