Microsoft excel 2007

1. Microsoft excel 2007 Dr. Yan Liu Department of Biomedical, Industrial and Human Factors Engineering Wright State University

2. Quick access toolbar Dialog box launcher Office button Tabs Ribbon (menu bars) Formula Status bar Worksheet tabs Main Window Worksheet

3. Office Button Clicking office button • Display commands which are related to the management of Excel and Excel worksheets (as opposed to managing the content) • Recent Excel files you have worked with • Click on an Excel file name to open it • Excel Options allow you to customize Excel

4. Ribbon • Contains all the commands for working with spreadsheet content • Commands related to a particular type of activity are grouped together • Divided into 7 tabs, each focusing on a major activity area • Ribbon is not customizable; you cannot change around the locations of the commands across the tabs or create new tabs • You can hide the ribbon of a particular tab by double-clicking it. Double-clicking it again to bring the ribbon back

5. Groups and Dialog Box Launcher Dialog box launcher Groups

6. More Button and Gallery • Clicking “more button” opens up a gallery, a collection of thumbnail graphics that offer you live previews • Live previews allow you see a preview of how an option will look on your worksheet without actually applying the changes

7. Quick Access Toolbar • The only permanent toolbar and located next to the office button • For holding the most frequently accessed commands • Can be customized • Click one of the suggested commands from the drop down list • Click on the More Commands option and use the dialogue box which opens (shown opposite) to add more commands into the box on the right

8. Status Bar Views Zooms Zoom dialog box Normal Page break preview Page Layout • Page Layout • Work and type into a worksheet whilst the page margins, headers and footers are displayed • All the displayed elements can be directly edited • Zooming Tools • Use the slider • Click the “+” or “-” button (increment/decrement of 10%) • Click on the Zoon Level box and select from the zoom dialog box

9. Worksheet Formula bar Entry line Name box Cell A1 Rows Columns • Cell: an intersection of a row and a column; has a unique address (cell reference) • Active cell: the currently selected cell • Each worksheet has 16,384 columns (labeled A to Z to AA to XFD) and 1,048, 576 rows • Workbook: opened when Excel is started; consists of several worksheets (default 3 for a new workbook) • Range of cells: A range of cells is selected by dragging with the mouse over the required cells Workspace Worksheets

10. Formula • A formula is a mathematical expression that tells Excel to perform an operation • Begins with the equal symbol (=) • Arithmetic operators (in the order of precedence) • Negation (-) • Exponentiation (^) • Multiplication and division (*, /) • Addition and subtraction (+, -) • The precedence order may be overridden by using parentheses

11. Suppose we have a laboratory heating apparatus with a thermometer calibrated in degrees of Fahrenheit. We need a table to give approximate Celsius values, shown as follows. It is known that Celsius = 5/9*(Fahrenheit – 32).

12. Equal sign Arguments = ROUND (A10, 2) Function name Using Functions • Functions • Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure • Begins with an equal sign (=) • Followed by the function name • Finally arguments for the function separated by commas, enclosed in a pair of parentheses

13. Zero argument: = PI () One argument: = SQRT (A2) Two arguments: = ROUND (A2,2) Variable arguments: = SUM (A1:A10) or = SUM(A1:A10,B3,B4) Nesting function: = SQRT(SUM (A1:A10)) Using Functions • Types of Functions • Ten categories • Mathematical & trigonometric, engineering, logical, statistical, date and time, database, financial, information, lookup and reference, and text • Arguments • The number of arguments in a function may be fixed, variable or even zero • The argument can also be a function itself (nesting function)

14. Statistical Analysis • Statistical Functions • Over 80 most commonly used statistical computations • Data Analysis Tools • If Analysis ToolPak is already installed, Data >> Data Analysis • Otherwise • Office Button >> Excel Options >> Add-ins • In the Manage box, select Excel Add-ins, click Go • In the Add-ins available box, select Analysis ToolPak checkbox, then click OK • Descriptive Statistics • Data >> Data Analysis >> Descriptive Statistics

17. Excel Solver • Load Solver • If Solver is already installed, Data >> Solver • Otherwise • Office Button >> Excel Options >> Add-ins • In the Manage box, select Excel Add-ins, click Go • In the Add-ins available box, select Solver Add-in checkbox, then click OK

18. Target Cell • The objective or goal • Maximize, minimize or set a specific value to the target cell • Changing Cells • The decision variables • Can be adjusted until the constraints in the problem are satisfied and the cell in the Set Target Cell box reaches its target • Constraints • The restrictions placed on the changing cells

19. In-Class Exercise Go to course website http://www.engineering.wright.edu/~yan.liu/ISE210/ to download In-Class Exercise Two. There are three problems in this exercise in three worksheets titled “Function”, “Descriptive Statistics” and “Optimization”.