IB ITGS THEORY (All the notes compiled from MS EXCEL Help ...



IB ITGS THEORY (All the notes compiled from MS EXCEL Help facility unless stated otherwise)

STRAND 3.8: SPREADSHEET, MODELING AND SIMULATION

What is a spreadsheet?

A spreadsheet consists of a grid of cells. A cell is a box on the spreadsheet into which numbers, text or calculations may be placed. The spreadsheet is divided into a number of rows and columns. Cells are formed where these cross each other. Each cell is referred to by a cell reference (or cell address). An example of this is B2. Think of it as being a bit like a map reference. This can be seen highlighted in the diagram below.

The row headings are down the side of the spreadsheet. Rows go across. The column headings are at the top of the spreadsheet. Columns go down.

What are spreadsheets used for?

Spreadsheets are used in a variety of situations where calculations need to be carried out.

The spreadsheet can be set up to carry out simulations based on constructed models.

A) SPREADSHEET CONCEPTS:

1. WHAT-IF SCENARIO / ANALYSIS

What-if analysis is a process of changing the values in cells to see how those changes affect the outcome of formulas on the worksheet. For example, varying the interest rate that is used in an amortization table to determine the amount of the payments. (From Microsoft Excel Help Facility)

A spreadsheet may be used for modelling situations. It may be used to predict what would happen if certain things happened, without risk. When we use a computer to do this, we are said to be doing a ‘What if’ analysis. Different values are input into the spreadsheet model to see what happens.

Examples of ‘What if’ analysis include…

Predicting what would happen to a company's profits if sales increased 10%

Predicting what would happen to your energy levels if you added an extra slice of toast at breakfast.

Predicting what would happen to your favourite team's league placing if they won the next two games.

Predicting what would happen to your overall grade if you added an extra 5% to your coursework marks.

WHAT-IF” ANALYSIS TOOLS:

a) SCENARIOS TOOL

(From Microsoft Excel Help Facility)

PRACTICAL EXERCISE A – HOME WORK – Do not submit, but very important for you

Start MS EXCEL application software. Then Click Scenarios on the Tools menu.

When the Scenario Manager dialog box opens, fill it up with relevant simulated data and see how excel Scenario manager works. USE MS Exel help facility if stuck.

b) GOAL SEEK TOOL

(From Microsoft Excel Help Facility)

PRACTICAL EXERCISE B – HOME WORK – Do not submit, but very important for you

Start MS EXCEL application software. Then Click Goal seek on the Tools menu.

When the Goal seek dialog box opens, fill it up with data in the box above and see how excel Goal seeking works. USE MS Exel help facility if stuck.

2. CELL REFERENCING TYPES

There are two types of cell referencing in spreadsheets: absolute and relative.

Absolute cell referencing is a type that refers to the content of a cell by the data in that cell. In excel we can use the $ (dollar) sign before and after the column name of a cell to show that we are referring to the specific cell.

Relative cell referencing is a type that refers to the content of a cell by the name in that cell.

When you use absolute reference, the content of the cell that this reference method has been used in does not change when the content of the cell being referred to changes; while if you use relative cell referencing method, the content of the cell that this reference method has been used in changes when the content of the cell being referred to changes.

3. DIFFERENCE BETWEEN VERIFICATION & VALIDATION

Validation is the checking of data before processing to ensure that it is acceptable for it or not. E.g. When entering a date, the validation for month is 1-12, you cannot enter 13, its out of range. Similarly a telephone number cannot contain letters.

Whereas Verification is the checking of data that has been copied from one place to another to ensure that is replaces the original one. E.g. PASSWORD. When you sign up in a site, just like WIKIANSWERS, you have to enter your password twice, the second entry being compared with the first.

From (accessed Tuesday, January 15, 2013)

In the modeling and simulation community, validation determines the degree of accuracy of associated data to the real world according to the model; verification determines whether a computer model and the associated content represent the developer’s conceptual descriptions and specifications.

From: (accessed Tuesday, January 15, 2013)

HOMEWORK

2009 May paper 1 Question 1 – Hand in Next Thursday:

[pic]

[pic]

[pic]

[pic]

B) ADVANCED SPREADSHEET FUNCTIONS:

1. LOOKUP

The LOOKUP function returns a value either from a one-row or one-column range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) or from an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.).

In MS EXCEL , The LOOKUP function has two syntax forms: the vector form and the array form.

i) Vector form

A vector is a range of only one row or one column. The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range. Use this form of the LOOKUP function when you want to specify the range that contains the values that you want to match. The other form of LOOKUP automatically looks in the first column or row.

[pic]

Examples of VECTOR LOOK UP

[pic]

ii) Array form

The array form of LOOKUP looks in the first row or column of an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) for the specified value and returns a value from the same position in the last row or column of the array. Use this form of LOOKUP when the values that you want to match are in the first row or column of the array. Use the other form of LOOKUP when you want to specify the location of the column or row.

 Tip   In general, it's best to use the HLOOKUP or VLOOKUP function instead of the array form of LOOKUP. This form of LOOKUP is provided for compatibility with other spreadsheet programs.

[pic]

Examples of VECTOR LOOK UP

[pic]

2. PIVOT TABLE

A PivotTable report is an interactive table that quickly combines and compares large amounts of data. You can rotate its rows and columns to see different summaries of the source data, and you can display the details for areas of interest.

AN EXAMPLE of PIVOT TABLE REPORT:

[pic]

A PivotTable report is an interactive table that quickly combines and compares large amounts of data. You can rotate its rows and columns to see different summaries of the source data, and you can display the details for areas of interest.

[pic]When should I use a PivotTable report?

Use a PivotTable report when you want to analyze related totals, especially when you have a long list of figures to sum and you want to compare several facts about each figure. In the report illustrated above, you can easily see how the third-quarter golf sales in cell F3 stack up against sales for another sport or quarter, or the total sales. Because a PivotTable report is interactive, you can change the view of the data to see more details or calculate different summaries, such as counts or averages.

[pic]How does it organize my data?

In a PivotTable report, each column or field in your source data becomes a PivotTable field that summarizes multiple rows of information. In the example above, the Sport column becomes the Sport field, and each record for Golf is summarized in a single Golf item.

A data field, such as Sum of Sales, provides the values to be summarized. Cell F3 in the report above contains the sum of the Sales value from every row in the source data for which the Sport column contains Golf and the Quarter column contains Qtr3.

[pic]How do I create a PivotTable report?

To create a PivotTable report, you run the PivotTable and PivotChart Wizard. In the wizard, you select the source data you want from your worksheet list or external database. The wizard then provides you with a worksheet area for the report and a list of the available fields. As you drag the fields from the list window to the outlined areas, Microsoft Excel summarizes and calculates the report for you automatically.

3. MACROS

[pic]Hide All

If you perform a task repeatedly in applications, you can automate the task by using a macro. A macro is a series of Word commands and instructions that you group together as a single command to accomplish a task automatically.

Here are some typical uses for macros:

• To speed up routine editing and formatting

• To combine multiple commands; for example, inserting a table with a specific size and

• borders, and with a specific number of rows and columns

• To make an option in a dialog box more accessible

• To automate a complex series of tasks

HOW TO create a macro

Record a macro (EXCEL 2007)

When you record a macro, the macro recorder records all the steps required to complete the actions that you want your macro to perform. Navigation on the Ribbon is not included in the recorded steps.

1. If the Developer tab is not available, do the following to display it:

a. Click the Microsoft Office Button[pic], and then click Excel Options.

a. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

2. To set the security level temporarily to enable all macros, do the following:

1. On the Developer tab, in the Code group, click Macro Security.

[pic]

2. Under Macro Settings, click Enable all macros (not recommended, potentially dangerous code can run), and then click OK.

Note To help prevent potentially dangerous code from running, we recommend that you return to any one of the settings that disable all macros after you finish working with macros. For more information about how to change the settings, see Change macro security settings in Excel.

3. On the Developer tab, in the Code group, click Record Macro.

4. In the Macro name box, enter a name for the macro.

Spaces cannot be used in a macro name; an underscore character works well as a word separator. If you use a macro name that is also a cell reference, you may get an error message that the macro name is not valid.

5. To assign a CTRL combination shortcut key (shortcut key: A function key or key combination, such as F5 or CTRL+A, that you use to carry out a menu command. In contrast, an access key is a key combination, such as ALT+F, that moves the focus to a menu, command, or control.) to run the macro, in the Shortcut key box, type any lowercase letter or uppercase letter that you want to use.

Note The shortcut key will override any equivalent default Excel shortcut key while the workbook that contains the macro is open. For a list of CTRL combination shortcut keys that are already assigned in Excel, see Excel shortcut and function keys.

6. In the Store macro in list, select the workbook where you want to store the macro.

7. In the Description box, type a description of the macro.

8. Click OK to start recording.

9. Perform the actions that you want to record.

10. On the Developer tab, in the Code group, click Stop Recording [pic].

Tip You can also click Stop Recording [pic]on the left side of the status bar.

How to run a macro

1. On the Developer tab, in the Code group, click Macros.

2. In the Macro name box, click the macro that you want to run.

3. Do one of the following:

• To run a macro in an Excel workbook, click Run.

Tip You can also press CTRL+F8 to run the macro. You can interrupt the execution of the macro by pressing ESC.

• To run a macro from a Microsoft Visual Basic module, click Edit, and then on the Run menu, click Run Sub/UserForm [pic], or press F5.

[pic]Hide All[pic][pic][pic][pic][pic][pic][pic][pic][pic][pic]

-----------------------

Column B

Row 2

Cell B2

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download