COM S Exam 2

Réussis tes devoirs et examens dès maintenant avec Quizwiz!

Which of these characters is not an acceptable character to use in a range name? - _ (underscore character) -z -! -1

How many variables does Goal Seek change with each time it is run?

1.) Output from Solver which displays on a new worksheet 2.) Organized structured table of scenarios, their input values, and their respective results that displays on a new worksheet 3.) Organizes the results from a set of scenarios in a PivotTable for further analysis 4.) Tool to input the set cell, to value, and by changing cell 5.) Finds the highest, lowest, or exact value for a particular results by adjusting variables

1.) Answer Report 2.) Scenario Summary Report 3.) Scenario PivotTable 4.) Goal Seek Dialog Box 5.) Optimization model

1.) The range of values to be used in an index function's first argument 2.) The first argument in the match function, the value to be found 3.) The second argument in the INDEX function 4.) The last argument in the INDEX function 5.) Return of the Match function

1.) Array 2.) Lookup value 3.) Row_num 4.) Column_num 5.) Position

1.) Solver must enforce this to solve the model. 2.) Cell containing a variable whose value changes until Solver optimizes the value in the objective cell. 3.) Enhances functionality in Excel 4.) Limit placed on the Solver model 5.) Manipulates variables based on constraints to find the optimal solution to a problem

1.) Binding Constraint 2.) Changing variable cell 3.) Add-in 4.) Constraint 5.) Solver

1.) A constraint that Solver must enforce to reach the target value. 2.) A cell containing a variable whose value changes until Solver optimizes the value in the objective cell. 3.) An add-in application that manipulates variables based on constraints to find the optimal solution to a problem.

1.) Binding constraint 2.) Changing variable cell 3.) Solver

1.) Show less detail 2.) Show more detail 3.) Show intermediate calculated values for a group of items 4.) The full summary of all items in a group 5.) Calculations like totals or averages

1.) Collapse 2.) Expand 3.) Subtotal 4.) Grand total 5.) Summary Statistics

1.) Averages values in a database column that match specified conditions. 2.) Adds values in a database column that match specified conditions. 3.) Calculates the fraction of a year between two dates based on the number whole days. 4.) Returns a value or reference to a value within a range based on the intersection of specific row and column.

1.) DAVERAGE function 2.) DSUM function 3.) YEARFRAC function 4.) INDEX function

1.) Calculates the number of days between two dates 2.) Returns the day of the month for a date value 3.) Returns the year for a date value 4.) Returns the percentage of a year past between two dates 5.) Returns the month number of a date value

1.) DAYS 2.) DAY 3.) YEAR 4.) YEARFRAC 5.) MONTH

1.) Database 2.) Financial 3.) Lookup & Reference 4.) Date & Time 5.) Logical

1.) DCOUNT 2.) IPMT 3.) INDEX 4.) YEARFRAC 5.) IF

1.) < 2.) + 3.) Calculates the difference from the median 4.) Mean 5.) >

1.) DMIN 2.) DSUM 3.) DSTDEV 4.) DAVERAGE 5.) DMAX

1.) Collection of related tables 2.) Where data is stored in a data model 3.) The field with matching data in two tables used to identify a correlation 4.) The link between the fields with matching data in two tables 5.) Function similar to how a data model matches related fields

1.) Data Model 2.) Table 3.) Related Field 4.) Relationship 5.) VLOOKUP

1.) The process of analyzing large volumes of data to identify trends and patterns in the data. 2.) An interactive table that uses calculations to consolidate and summarize data from data source into a separate table to enable a person to analyze the data in dataset without altering the actual data. 3.) A user-defined field that performs a calculation based on other fields in a PivotTable. 4.) A window listing all unique items in a field so that the user can click button to filter data by that particular item or value.

1.) Data mining 2.) Pivottable report 3.) Calculated Field 4.) Slicer

1.) The underlying data of a PivotTable 2.) Related tables 3.) Evaluating a large dataset looking for trends 4.) Hierarchical structure 5.) A tool to organize items in an outline

1.) Data set 2.) Data model 3.) Data Mining 4.) Outline 5.) Group

1.) An INDEX function returns the position of a value. 2.) The PPMT function calculates the monthly principal and interest payment of a loan or investment. 3.) A MATCH function returns the value of a given position. 4.) An empty cell in the criteria in an Advanced Filter will display no records. 5.) A loan amortization table is a schedule that calculates the interest per payment period, principal repayment for each payment, and remaining balance after each payment is made. 6.) The NPV function is used mostly to calculate return rates for IRAs. 7.) This is a correct example of the structure of an IPMT function. =IPMT(rate,per,nper,pv,[fv],[type]) 8.) The real functionality of MATCH and INDEX functions comes from nesting them. 9.) NPER calculates the periodic interest rate of an investment or loan. 10.) Database functions do not require a Criteria Range. 11.) The Arguments of an INDEX function are array, row_num, and column_num.

1.) F 2.) F 3.) F 4.) F 5.) T 6.) F 7.) T 8.) T 9.) F 10.) F 11.) T

1.) When the underlying data changes, a PivotTable automatically updates. 2.) A calculated field must be built in the original data set. 3.) The % Period Expense Column on this PivotTable uses a % of Parent Row Total calculation option. 4.) In a Subtotaled Worksheet, this is a correct example of a subtotal function in cell B90 which sums B80:B89 = SUBTOTAL(9, B80:B89) 5.) A PivotTable has a live update feature which refreshes the data in real time as the dataset changes. 6.) A slicer has a window with a button for each item in a field. 7.) You cannot create custom calculations in a PivotTable. 8.) A PivotTable can only display one summary statistic at a time. 9.) Displaying subtotals at the top or bottom of a section is controlled on the PivotTable styles gallery. 10.) Excel is able to group data both by rows and columns in a subtotal.

1.) False 2.) False 3.) True 4.) True 5.) False 6.) True 7.) False 8.) False 9.) False 10.) True

1.) Category (Regular Meal, Snacks, etc.) 2.)Item (Pizza Slice, Cookies, Water, etc.) 3.) Extended Price (unit price * quantity of item sold) 4.) Quantity (quantity of item sold) 5.) Date (date of the purchase)

1.) First in rows 2.) Second in rows 3.) Second in values 4.) first in values 5.) filters or not used at all

1.) You want to know what interest rate you need to find to make a loan payment exactly $500 per month 2.) You want to know how many products your production line can make with the least waste 3.) You want to compare the total interest paid on the lifetime of a loan with varying interest rates and terms 4.) You want to compare different calculations like monthly payment and total interest paid on a loan with the different interest rates 5.) You want to compare best-case, worst case, and most-likely case scenarios for a business decision

1.) Goal Seek 2.) Solver 3.) Two-variable data table 4.) One-variable data table 5.) Scenario Manager

1.) Returns the value of the intersection of a specified row and column 2.) Returns true if any criteria are true 3.) Returns the position of a particular value 4.) Decision structure 5.) Returns the interest rate of a loan or investment

1.) INDEX 2.) OR 3.) MATCH 4.) IF 5.) RATE

1.) Periodic interest at a point in time of a loan. 2.) The total amount of principal paid on a loan at a point in time. 3.) The total amount of interest paid on a loan at a point in time. 4.) Principal paid on a loan at a point in time. 5.) Visually organize the payments in a loan.

1.) IPMT 2.) CUMPRINC 3.) CUMIPMT 4.) PPMT 5.) Amortization table

1.) Calculates periodic interest for a specific payment period on a loan or investment with a fixed rate, a specified term, and identical periodic payments. 2.) Counts the cells that contain a number in a database column that matches specified conditions. 3.) Identifies the highest value in a database column that matches specified conditions. 4.) Identifies the lowest value in a database column that matches specified conditions.

1.) IPMT function 2.) DCOUNT function 3.) DMAX function 4.) DMIN function

1.) Identifies the position of a value in a list based on a lookup value, lookup array, and a match type. 2.) Returns TRUE if the argument is false and FALSE if the argument is true. 3.) Returns TRUE if the argument is true and FALSE if the argument is false. 4.) Returns TRUE when all arguments are true and FALSE when at least one is false.

1.) MATCH function 2.) NOT function 3.) OR function 4.) AND function

1.) 10YearGoal 2.) Ten Year Goal 3.) Ten_Year_Goal! 4.) Goal10Year 5.) Ten_Year_Goal

1.) Must start with a letter 2.) Spaces are not allowed 3.) No special characters 4.) No problem, numbers used appropriately 5.) No problem, underscore used for readability

1.) Calculates the number of period for an investment or loan given a fixed rate, period payment, and present value. 2.) Calculates the future value of an investment given a fixed rate, a term, and identical periodic payments. 3.) Calculates the net present value of an investment with a fixed rate and periodic payments that may be identical or different. 4.) Calculates cumulative principle for a specified payment period.

1.) NPER function 2.) FV function 3.) NPV function 4.) CUMPRINC function

1.) Use the results of one function as an argument in another 2.) All conditions must be met for a true return 3.) At least one condition must be for a true return 4.) False = true 5.) Tests conditions to process decisions

1.) Nested Function 2.) AND 3.) Or 4.) NOT 5.) IF

1.) Default value shows the raw summary of the value. 2.) Each value displays as a percentage of the whole and adds up to 100%. 3.) Each value displays as a percentage of the column 4.)Each value shows as a percentage of the row. 5.)Each value displays as a percentage of the hieararchical level above it.

1.) No calculation 2.) Percent of grand total 3.) Percent of column total 4.) Percent of row total 5.) Percent of parent row total

1.) <> 2.) > 3.) < 4.) <= 5.) >=

1.) Not equal 2.) Greater than 3.) Less than 4.) Less than or equal to 5.) Greater than or equal to

1.) Cell that contains the formula to produce a value to be optimized 2.) An Excel add-in that searches for the best or optimum solution to a problem by manipulating the values for several variables within restrictions that you impose 3.) Find the highest, lowest, or exact value for one particular result by adjusting values for selected variables 4.) A limit that Solver must follow to find the optimal value 5.) A constraint that does not restrict the target value that Solver finds

1.) Objective Cell 2.) Solver 3.) Optimization model 4.) Binding constraint 5.) non-binding constraint

1.) A data analysis tool that provides various results based on changing one variable. 2.) A set of values that represent a possible situation. 3.) The cell that contains the formula-based value that you want to maximize, minimize, or set to a value in Solver.

1.) One-variable data table 2.) Scenario 3.) Objective Cell

1.) A structured range that contains different values for one variable to compare how these values affect one or more calculated results 2.) A structured range that contains different values for two variables to compare how these differing values affect the results for one calculated value 3.) Solves for a desired result of a formula by changing values 4.) Sets up and compares multiple situations 5.) Finds the best way to optimize values within specified constraints

1.) One-variable data table 2.) Two-variable data table 3.) Goal Seek 4.) Scenario Manager 5.) Solver

1.) Finds the highest, lowest, or exact value for one particular result by adjusting values for selected variables. 2.) A constraint that does not restrict the target value that Solver finds. 3.) The process of changing variables to observe how changes affect calculated results. 4.) A value that you can change to see how that change affects other values.

1.) Optimization model 2.) Nonbinding constraint 3.) What-if analysis 4.) Variable

1.) Calculates the total present (current) value of an investment with a fixed rate, specified number of payment periods, and a series of identical payments that will be made in the future 2.) Calculates the future value of an investment, given a fixed interest rate, term, and periodic payment 3.) Calculates the periodic rate for an investment or loan given the number of payment periods, a fixed periodic payment, and present value 4.) Calculates the number of payment periods for an investment or loan given a fixed interest rate, periodic payment, and present value 5.) Calculates the net present value of an investment, given a fixed rate (rate of return) and future payments that may be identical or different

1.) PV 2.) FV 3.) RATE 4.) NPER 5.) NPV

1.) Calculates the present value of an investment with a fixed rate, specified number of periods, and identical periodic payments that will be made in the future. 2.) Calculates cumulative interest for a specified payment period. 3.) A schedule showing monthly payments, interest per payment, amount toward paying off the loan, and the remaining balance for each payment. 4.) Calculates the principle payment for a specified period on a loan or an investment given a fixed rate, a specified term, and identical periodic payments.

1.) PV function 2.) CUMIPMT function 3.) Loan amortization table 4.) PPMT function

1.) A graphical representation of aggregated data derived from a PivotTable. 2.) A task pane that displays the fields in a dataset and enables a user to specify what fields are used to create a layout to organize the data in columns, rows, values, and filters in a PivotTable. 3.) The text or field name that appears as a header or title at the top of a slicer to identify the data in that field.

1.) Pivot Chart 2.) PivotTable field list 3.) Slicer caption

1.) Change the appearance of a PivotTable's color, font effects and shading 2.) Determine which totals and subtotals show 3.) Change the colors and backgrounds of PivotCharts 4.) Change the layout of the elements on a PivotChart 5.) Change the color scheme of a PivotChart

1.) PivotTable Styles 2.) PivotTable Layout 3.) PivotChart Styles 4.) Quick Layout 5.) Change Colors

1.) A small window that starts with the first date and ends with the last date in the data source. It contains horizontal tiles that you can click to filter data by day, month, quarter, or year. 2.) A collection of related tables that contain structured data used to create a database. 3.) A set of formatting that controls bold, font color, shading colors, and border lines.

1.) PivotTable timeline 2.) Data Model 3.) PivotTable Style

1.) AXIS (CATEGORY) 2.) LEGEND (SERIES) 3.) FILTERS 4.) VALUES 5.) Plot Area

1.) ROW 2.) COLUMNS 3.) FILTERS 4.) VALUES 5.) Pivot Table

1.) A section within the PivotTable Fields list used to place a field that will display labels to organize data horizontally in a PivotTable. 2.) A section within the PivotTable Fields list used to place a field to display summary statistics, such as totals or averages in a PivotTable. 3.) A section within the PivotTable Fields list used to place a field so that the user can then filter the data by that field. 4.) A section within the PivotTable Fields list used to place a field that will display labels to organize summarized data vertically in a PivotTable.

1.) ROWS area 2.) VALUES area 3.) FILTERS area 4.) COLUMN area

1.) Memorable name substitution for formulas 2.) Enables you to experiment with different variables to observe how they affect the outcome 3.) The value that is changed in what-if analysis 4.) Value increments when filling in a series 5.) The ending value for the series

1.) Range name 2.) What-if analysis 3.) Subsitituion value 4.) Step value 5.) Stop value

1.) An association created between two tables where both tables contain a common field of data. 2.) A hierarchical structure of data that you can group related data to summarize. 3.) A row that contains at least one aggregate calculation, such as SUM or AVERAGE, that applies for a group of sorted data within a dataset. 4.) The process of joining related rows or columns of related data into a single entity so that groups can be collapsed or expanded.

1.) Relationship 2.) Outline 3.) Subtotal 4.) Grouping

1.) The field(s) used to organize a PivotTable along the left side of the table 2.) The field(s) used to organize PivotTable along the top of the table 3.) The first field in this area creates a filter that appears in cell B1. 4.) A small window containing one button for each unique item in a field 5.) Fields added to this area have a calculation of some sort applied.

1.) Rows area 2.) Columns area 3.) Filters area 4.) Slicer 5.) Values area

1.) Values that represent different possible situations 2.) Tool that enables you to build different scenarios for comparison 3.) Displays different scenarios with their input values and results well organized on a new worksheet 4.) Variable 5.) An optional output

1.) Scenario 2.) Scenario Manager 3.) Scenario Summary Report 4.) Changing Cell 5.) Scenario Summary PivotTable

1.) Answer Report 2.) PivotTable 3.) Set Cell 4.) Change interest rate 5.) Change term and down payment

1.) Solver 2.) Scenario Manager 3.) Goal Seek 4.) One-variable data table 5.) Two-variable data table

1.) Replaces the original value of a variable in a data table 2.) The process of changing variables to observe how changes affect calculated results 3.) A cell containing a variable whose value changes until Solver optimizes the value in the objective cell 4.) A word or string of characters that represents one or more cells 5.) A value that you can change to see how that change affects other values

1.) Substitution Value 2.) What-if analysis 3.) Changing variable cell 4.) Range name 5.) Variable

1.) Replaces the original value of a variable on a data table. 2.) A limitation that imposes restrictions on Solver. 3.) A program that can be added to Excel to provide enhanced functionality. 4.) A worksheet that contains scenario results.

1.) Substitution value 2.) Constraint 3.) Add-in 4.) Scenario summary report

1.) Adds the values in the field together 2.) Adds the values in the field together and then divides by the number of values 3.) The greatest value 4.) The least value 5.) The number of values

1.) Sum 2.) Average 3.) Max 4.) Min 5.) Count

1.) + 2.) > 3.) < 4.) * 5.) (1, 2, 3, 4...)

1.) Sum 2.) Max 3.) Min 4.) Product 5.) Count

1.) Excel uses two substitution values laid out in a grid in two-variable data table. 2.) An objective cell is required to complete a Solver model. 3.) Scenario Manager is not an Excel Add-in. 4.) Optimization models find the highest, lowest, or exact value for one particular result. 5.) What-if analysis always provides a definitive solution to a problem. 6.) A variable is an input value that changes the outcome of the situation. 7.) Solver has the ability to generate an answer report. 8.) Solver is an Excel Add-in. 9.) You do need to use an absolute cell reference when copying formulas with named ranges. 10.) Discount Range is a valid range name. 11.) Excel must have at least one formula in one-variable data table.

1.) True 2.) True 3.) True 4.) True 5.) False 6.) True 7.) True 8.) True 9.) False 10.) False 11.) True

1.) A data analysis tool that provides results based on changing two variables. 2.) A tool that identifies the necessary input to obtain a desired goal. 3.) Enables you to define and manage scenarios to compare how they affect results. 4.) A word or string of characters that represents one or more cells.

1.) Two-variable data table 2.) Goal Seek 3.) Scenario Manager 4.) Range name

If cell B5 contains the value 6 and is named Quantity and cell C6 contains the value 3 and is named Unit_Price, what would the formula =Quantity*Unit_Price display in D6?

The original mortgage loan was for $300,000 with a 5% APR for 30 years. You want to calculate the interest on the last monthly payment at the end of the 15th year. What value should be referenced for the per argument in the IPMT function?

180

Which of the following is not an acceptable range name?

2018_Rate

If there are 5 possible outcomes, how many logical tests are required?

If you use the function, =MATCH(66, A1:A4, 0), on the table below, what would be the result?

If 9/3/2021 is the contents of B3, what would be the result of =MONTH(B3)?

To set up this two-variable data table, what should be in B9?

=$E$5

Which PivotTable calculated field is correctly constructed to calculate a 20% tip on a meal at a restaurant?

='Meal Cost' * 0.2

The date 5/12/2018 is stored in cell C1. What is function should you use to extract just 12?

=DAY(C1)

Today's date (February 12, 2018) is stored in cell B1. The last day of the semester (May 4, 2018) is stored in cell B2. You want to know how many days until the end of the semester? Which function you should use?

=DAYS(B2,B1)

Which function calculates the Reduction in C2 to be filled through the rest of the reduction column if Silver members have a 5% discount, Gold 10%, and Platinum have 20%?

=IF(A2="Silver", B2*0.05, IF(A2="Gold", B2*0.1, B2*0.2))

A local police office wants to create a rule that if an officer pulls over a person for exceeding the speed limit by at least five miles per hour or if that person has two or more speeding violations on record, the officer will fine the speeder the higher of $200 or $50 for each mile over the speed limit. Otherwise, the fine is $45. The speed limit is entered in cell B5, the person's speed is entered in cell B10, and the person's number of previous ticket is entered in cell B11. What function derives the correct answer?

=IF(OR(B10-B5>=5,B11>=2),MAX(200,(B10-B5)*50),45)

Your workbook contains a list of artwork. Column B lists Sold Out if the piece is sold out, Available if it is still readily available, or Limited if only a few pieces are available. In the third column, you want to display TRUE if the art is not sold out. What function would produce the same results as =OR(B1="Available", B1="Limited")?

=NOT(B1="Sold Out")

Which of the following is true about creating a blank PivotTable?

A PivotTable may be created on the same worksheet as the source data.

Which function is not a Subtotal function?

AGGREGATE

You created a slicer on the field State. What items would you be most likely to see in the slicer?

AL, CA, GA, KY, MS, NC, OR, and VA

A subtotal row must contain at least one .................. function.

Aggregate

Given this worksheet, which cell would you use in the set cell: box in Goal Seek to see how expensive of a house you can afford and keep your Mortgage Payment at $650?

What are the row and column input cells in this table?

B5 and B4

What settings would you select for a PivotTable if you want to apply a different color scheme and display different fill colors for the main category rows and horizontal lines within the PivotTable?

Banded Rows check box and a different PivotTable style

What function would you use to calculate the total interest paid for the first year of a mortgage?

CUMIPMT

Which function calculates the total amount of interest paid over a specific number of payments?

CUMIPMT

Which function calculates the total principal through a specified number of payments?

COM S Exam 2

Ensembles d'études connexes

Macro midterm

POLS 1101 - Quiz 8

ged - math

Chapter 15: Assessing Head & Neck

FIN 300 Test 2

NR222 Final Exam Prep

Business Statistics Test 2

CH. 7

lionel messi

Chapter 1- Psychology

Kinn's Administrative Medical Assistant - Chapter 15

SC 545

The Electoral College

10,11,12) Redox Potential

Quiz 10

Posi2310 test 3

Cars- Personal Finance

Network+ 8th Edition Chapter 4

BIS 228 Exam 2

NCLEX 200 SATA