Study Guides (248,609)
Canada (121,634)
Dr.Hurst (16)
Final

Computer Science Exam Review.docx

41 Pages
469 Views

Department
Computer Science
Course Code
COMPSCI 1BA3
Professor
Dr.Hurst

This preview shows pages 1,2,3,4. Sign up to view the full 41 pages of the document.
Description
Computer Science Exam Review An Excel Table is a structured range of data that is managed independently from the rest of the data in a worksheet Subtotals can only be applied to ranges You can replace the specific cell or range address with a structured reference, the actual table name or column header Qualified vs. unqualified references -Unqualified references Dont include the table name -You can use unqualified references in a calculated column or formula within an Excel table, however you need to use qualified references outside the table or in another worksheet when referring to an Excel table or a portion of the table. PivotTables allow us to summarize data into categories using functions (detailed data is not shown) Values fields- the fields containing the data to be summarized Category fields- the fields that the value fields will be grouped by -Rows -Columns IF Function: -Evaluates whether a condition is true or false (called a logical test) and returns one value if the condition is true, and another value if the condition is false. If true then value a else value b Syntax: =IF(logical_test, value_if_true, *value_if_false+) AND Function Allows you to test multiple conditions Returns either TRUE or FALSE Only returns TRUE if all tested values are TRUE Syntax: =AND (logical1 [,logical2]) Can nest In an IF statement as the logical test Remainer =IF(AND(A1=1,A2=1),yes OR Function Allows you to test multiple conditions Returns either TRUE or FALSE Returns TRUE if any tested values are TRUE and FALSE if all of the tested values are FALSE Syntax: =OR (logical1 *,logical2+) Can nest in an IF statement as the logical test Remainder, =IF(logical_test, Value_if_true, [value_if_false] A nested IF function is when one IF function is placed inside another IF function to test an additional condition What is a lookup table? A table that organizes data you want to retrieve into different categories -The categories for the lookup table, called compare values, are located in the tables first column or row -To retrieve a particular value from the table, a lookup value (the value you are trying to find) needs to match the compare values Using the lookup table to find an approximate match Searches the first column of the table until it locates the largest value that is less than the lookup value Using the lookup table to find an exact match VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Conditional Formatting: Allows you to change a cells formatting when its contents match a specified condition Rules manager: A rule specifies the type of condition (such as formatting cells greater than a specified value), the type of formatting when that condition occurs Conditional Formatting: -Allows you to change a cells formatting when its contents match a specified condition. -When would you use this? To highlight those cells whose values may warrant action E.g., duplicate records, negative balances, overdue dates Error values such as #DIV/0!, #N/A, and #VALUE! indicate that some element in a formula or a cell referenced in a formula is preventing Excel from returning a calculated value Cost-volume-profit (CVP) analysis (sometimes called a break event analysis) expresses the relationship between a companys expenses, its volume of business, and the resulting profit or net income Types of expenses: Variable expenses change in proportion to the amount of business a company does (i.e. manufacturing materials) A fixed expense is an expense that must be paid regardless of sales volume (i.e. rent) What if analysis: Change the value of an input cell and observe the effects on calculated cells Goal seeking: Specify the value for a calculated cell, returns the input value needed to reach that goal Very useful for doing simple queries to see what the effects are of changing the value of one variable For more complex analyses, one could do several rounds of Goal Seeking A more efficient approach is to use a data table A data table organizes the results of several what-if analyses within a single table One variable data table Specify one input cell and any number of result cells Two variable data table Analyzes a variety of combinations simultaneously Uses two input cells, but displays only a single result value What is a scenario? A set of input values used to perform a what-if analysis Need to create scenarios if the what-if-analysis is more than two input cells Scenario Manager is a feature within Excel that enables you to create as many scenarios as you want, easily switching between the different scenarios to display the results of several what-if analyses. A scenario summary report lists the values for the changing and result cells for each scenario Questions on test possibly. Session 1.1 1. What are the 2 types of sheets used in a workbook? Chart sheets and worksheets 2. List 2 ways of identifying the active cell in the worksheet. The active cell is surrounded by a thick border and its cell reference appears in the Name box. 3. What is the cell reference for the cell located in the fourth column in the third row? D3 4. What keyboard shortcut changes the active cell to cell A1? The Ctrl+Home keys 5. What is text data? A combination of alphanumerical characters that form words and sentences (called a text string) 6. What keys do you press to start a new line of text within a cell? Enter the first line of text, press the Alt+Enter keys, and then type the second line of text. 7. A cell A4 contains may.3 2006; why doesnt excel consider this data a text string? Because its a date; all dates are numbers formatted to appear in standard date formats. 8. Explaining the difference between clearing a row and deleting a row? Clearing a row removes only the contents of the row, deleting a row removes the contents and the row. Session 1.2 1. Describe the 2 types of ranges in excel. Adjacent ranges contain a rectangular block of cells; nonadjacent ranges contain a collection of adjacent ranges. 2. What is the range reference for cells A3 through G5 and J3 through M5? A3:G5;J3:M5 3. What formula would you enter to add the values in cells B4, B5, B6? What fuction would you enter to achieve the same result? =B4+B5+B6; =SUM(B4:B6) 4. How do you rename a worksheet? Double-click the sheet tab, and then type a new name on the sheet tab.
More Less
Unlock Document

Only pages 1,2,3,4 are available for preview. Some parts have been intentionally blurred.

Unlock Document
You're Reading a Preview

Unlock to view full version

Unlock Document

Log In


OR

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


OR

By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.


Submit