Excel Pretest - 123 PDF

Preview

Summary

123...

Description

#

Question

Poin ts

Corre ct?

Submitted Answer

1

Click the Name Box.

1/1

You clicked the Nam

2

Apply the Accounting Number Format to the selected cells.

1/1

In the Home Ribbon the Number Forma Format menu, youse

3

Apply the Short Date format similar to 7/1/2016 to the selected cells.

1/1

In the Home Ribbon the Number Forma Format menu, youse

4

Apply the date number format to the selected cells to display dates in the format similar to 14-Mar.

1/1

In the Home Ribbon Number Dialog Lau the Category list, yo Cells dialog from the the Format Cells dial

5

Use AutoFill to complete the series from cell B4 through cell D4.

1/1

You dragged the Fill

6

Edit the formula in cell B9 so the references to cell E2 will update when the formula is copied, and the reference to cell B8will remain constant. Use AutoFill to copy the formula to cells B10:B12.

1/1

You clicked the form cell C9, clicked cell B typed "=E2*$B$8" i Handletool.

7

Use AutoSum to enter a SUM function in the selected cell.

1/1

In the Home Ribbon the AutoSum button

8

Create a new file based on the Personal expenses calculatortemplate.

1/1

You opened the back the Personal expen

9

Remove the fill color from the selected cells.

1/1

In the Home Ribbon Colorbutton arrow. I

1 0

Apply the Calculation cell style to the selected cells.

1/1

In the Home Ribbon Styles button. In the

1 1

Use Format Painter to copy the formatting from cell E1 and apply it to cell F1.

1/1

In the Home Ribbon the Format Painter

#

Question

Poin ts

Corre ct?

Submitted Answer

1 2

Apply conditional formatting to the selected cells using the blue solid fill data bar.

1/1

In the Home Ribbon the Conditional For the Data Bars menu, Gradient Blue optio

1 3

Apply conditional formatting to the selected cells using the Three Symbols (Circled) icon set (the first icon set in the Indicators section).

1/1

In the Home Ribbon the Conditional For the Icon Sets menu, Symbols Circled op

1 4

Apply conditional formatting so cells with a value greater than the average are formatted using a yellow fill with dark yellow text.

1/1

Inside the Above Ave withdrop-down. Insi range withdrop-down Text list item. Inside

1 5

Replace all instances of the word Spa Pool in this worksheet with Pool. Do not replace them one at a time. Close the dialog when you are finished.

1/1

Inside the Find and R Pool", updated the R Allbutton. Inside the the Find and Replace

1 6

Set the print area.

1/1

In the Page Layout R the Breaks button, c Area menu, youclick

1 7

Change the color of the sheet tab for the PO Q4 worksheet to Blue, Accent 1, Lighter 60% (the fifth option in the third row of theme colors).

1/1

You clicked the PO Q clicked the PO Q4 ta the PO Q4 tab, right the Tab Right Click m Accent 1, Lighter 6

1 8

Copy the Car Loan worksheet to a new workbook.

1/1

In the Tab Right Click Inside the Move Or C clicked the To book book drop-down, you Copy dialog, you clic

1 9

Group together the Q1-Q2 and Q3-Q4 worksheets.

1/1

You shift-clicked the

#

Question

Poin ts

Corre ct?

Submitted Answer

2 0

Autofit column F to best fit the data.

1/1

You double-clicked c

2 1

Freeze the top row of the worksheet.

1/1

In the View Ribbon Ta Panes button. In the Panesmenu item.

2 2

Unhide column E.

1/1

You clicked on F, shi the Cells Ribbon Gro in the Hide & Unhide

2 3

Switch to Page Break Preview view.

1/1

You clicked the Page

2 4

Modify the PO Q4 worksheet so gridlines will print.

1/1

In the Page Layout R Group, youchecked t

2 5

Change the scaling option so all columns will print on one page.

1/1

In the Page Layout R the Width: drop-do pagemenu item.

2 6

Set row 1 to print on every page.

1/1

Inside the Page Setu Inputcollapsible inp Setupdialog, you clic the Page Setup dialo

2 7

Enter a formula in cell E4 to calculate the average value of cells B4:D4.

1/1

You typed =Aver in pressed Enter.

2 8

In cell H12, enter a formula to find the highest percentage of items received in the order (cells H2:H11).

1/1

You typed =MAX(H2

2 9

In cell H12, enter a formula to find the lowest percentage of items received in the order (cells H2:H11).

1/1

You typed =MIN(H2

3 0

Insert the current date and time in the selected cell.

1/1

In the Formulas Ribb the Text button, clic & Trig button, and c

#

Question

Poin ts

Corre ct?

Submitted Answer

Time menu, you clic Arguments dialog, y 3 1

Insert the current date in the selected cell. Do not include the current time.

1/1

In the Formulas Ribb the Date & Time bu the TODAYmenu ite the OK button.

3 2

In cell C12, enter a formula using a counting function to count the number of items in the Item column (cells C2:C11).

1/1

You typed =Counta(

3 3

In cell G12, enter a formula using a counting function to count the number of blank cells in the Received column (cellsG2:G11).

1/1

You typed =COUNTB

3 4

In cell F12, enter a formula using a counting function to count numbers in the Ordered column (cells F2:F11).

1/1

You typed =COUNT(

3 5

Enter a formula in cell B8 to display the text from cell A8 in all lower case letters.

1/1

You typed =LOWER(

3 6

Enter a formula in cell B8 to display the text from cell A8 in proper case with only the first letter of each word in upper case.

1/1

You typed =Proper

3 7

Enter a formula in cell B8 to display the text from cell A8 in all upper case letters.

1/1

You typed =uppe in

3 8

On the Summary sheet, in cell B3, enter a formula to display the value of cell B3 from the ByMonth sheet.

1/1

You typed = in cell B and clicked cell B3.

3 9

Name cell B1 as follows: BonusRate

1/1

In the Formulas Ribb the Define Name b the Define Name... the OKbutton.

4 0

Use the Create from Selection command to create named ranges for the data table B8:E11 using the

1/1

In the Formulas Ribb the Create from Se

#

Question

Poin ts

labels in row 1 as the basis for the names.

Corre ct?

Submitted Answer

Selection dialog, you

4 1

Enter a formula in cell B2 to calculate Ken Dishner's bonus for the first quarter. Multiply his sales total (cell E8) times the bonus rate (the cell named BonusRate).

1/1

You typed =E8*Bo in pressed Enter.

4 2

Edit the Bonuses named range so it refers to cells B2:B5 on the Bonus worksheet. Close the Name Manager when you are finished.

1/1

In the Formulas Ribb the Name Manager the close dialog bu Refers Input, clicked

4 3

Enter a formula in cell C2 to return a value of yes if the value in cell E8 is greater than or equal to the value in B2 or no if it is not.

1/1

In the Logical menu, Arguments dialog, y the If Logical Test Fo Input, typed "no" in Value If False Form

4 4

Using cell references, enter a formula in cell B7 to calculate monthly payments for the loan described in this worksheet. Use a negative value for the Pv argument.

1/1

In the Financial men Arguments dialog, y the Pmt Rate Formul typed -B4 in the Pmt

4 5

Enter a formula in cell B2 using the VLOOKUP function to find the total sales for the date in cell B1. Use the name DailySalesfor the lookup table. The total sales are located in column 5 of the lookup table. Be sure to require an exact match.

1/1

Inside the Function A typed False in the V the Vlookup Lookup Table Array Formula Input, and clicked th

4 6

Show the tracer arrows from cell B5 to the cell(s) that are dependent on it (cells containing formulas that reference the value or formula in cell B5).

1/1

In the Formulas Ribb Group, you clicked th

4 7

Display the formulas in this worksheet.

1/1

In the Formulas Ribb Group, you clicked th

4 8

Enter a formula in cell D2 to calculate C2/C14 rounded to 3 decimal places.

1/1

You typed =Round(C

#

Question

Poin ts

Corre ct?

Submitted Answer

4 9

Enter a formula in cell B1 using the SUMPRODUCT function to calculate the total value of the current leases by multiplying the current monthly rents by the remaining months on each lease. Use the range names SpecialPrice and MembershipsSold.

1/1

Inside the Insert Fun the Function Argume typed SpecialPrice typed Memberships clicked the OK butto

5 0

Enter a formula in cell E2 using SUMIFS to calculate the total price (use the named range JunePrices) where the value in the JunePOs named range is equal to the value in cell D1 and the value in the JuneCompanies named range is equal to "Salon Supplies".

1/1

Inside the Function A typed JunePrices in typed JunePOs in th the Sumifs Criteria1 Criteria Range2 Form Criteria2 Formula Inp

5 1

Enter a formula in cell E1 using SUMIF to calculate the total quantity in stock for items from the company "ColorFab". Use the range name Company for the Range argument, the text string "ColorFab" for the Criteria argument, and InStock for the Sum_range argument.

1/1

Inside the Insert Fun the Function Argume typed Company in t the Sumif Criteria Fo Formula Input, and c

5 2

Enter a formula in cell E1 using AVERAGEIF to calculate the average quantity in stock for items from the company DermoMagic. Use the range name Company for the Range argument, the text string "DermoMagic" for the Criteria argument, and InStock for the Average_range argument.

1/1

Inside the Insert Fun the Function Argume typed Company in t typed "DermoMagi typed Instock in the the OK button.

5 3

Enter a formula in cell E2 using AVERAGEIFS to calculate the average price (use the named range JunePrices) where the value in the JunePOs named range is equal to the value in cell D1 and the value in the JuneCompanies named range is equal to the text string "DermoMagic".

1/1

Inside the Function A typed JunePrices in typed JunePOs in th the Averageifs Criter the Averageifs Criter the Averageifs Criter

5 4

In cell H1, enter a formula using COUNTIF to count the number of cells in the range named Delivery Time that have a value greater than 14. Remember to enclose the expression in quotation marks.

1/1

Inside the Insert Fun the COUNTIF list ite the OKbutton. Inside dialogbutton, typed

#

Question

Poin ts

Corre ct?

Submitted Answer

typed ">14"in the C 5 5

In cell H2, enter a formula using COUNTIFS to count the number of rows where values in the range named Delivery Time have a value greater than 14 and cells in the range named ReorderStatus display "no".

1/1

Inside the Function A typed Delivery Tim typed ">14" in the C typed ReorderStatu typed "no" in the Co the OK button.

5 6

Enter a formula in cell E2 to calculate the median value of the prices in the cell range C6:C14.

1/1

You typed =MEDIAN

5 7

Enter a formula in cell C1 to calculate the standard deviation of the values in cells C5:C13. Assume this array is a sample of a larger set of values.

1/1

In the More Function Smenu item. Inside t dialog button, typed keydowned the Stde

5 8

Enter a formula in cell C1 to find the rank of the value in cell C9 compared to the values in cells C5:C13.

1/1

In the More Function Eqmenu item. Inside dialog button, typed typed C5:C13in the the Rank.eq Ref Fo

5 9

In cell D6, enter a formula using OR to display TRUE if the daily sales (cell C6) is greater than the overall average (cell C3) orthe daily sales (cell C6) is greater than the employee's average (cell C4). Use cell references and enter the arguments exactly as described in this question.

1/1

In the Logical menu, Arguments dialog, y the Or Logical1 Form Input, and keydowne

6 0

In cell D6, enter a formula using AND to display TRUE if the daily sales (cell C6) is greater than the overall average (cell C3) and the daily sales (cell C6) is greater than the employee's average (cell C4). Use cell references and enter the arguments exactly as described in this question.

1/1

In the Logical menu, Arguments dialog, y the And Logical1 For Input, and keydowne

6 1

In cell B6, enter a formula to calculate the future value of this savings strategy. Use cell references wherever

1/1

In the Financial men Arguments dialog, y

#

Question

Poin ts

Corre ct?

Submitted Answer

the Fv Rate Formula B3 in the Fv Pmt For Input collapsible inp

possible. The annual interest rate is stored in cell B5, the number of payments in cell B4, and the monthly payment amount in cell B3. Remember to divide the annual interest rate by 12 and use a negative value for the Pmt argument. 6 2

In cell B10, enter a formula using PV to calculate the value today (the present value) of the four-year tuition plan. Use cell references wherever possible. The annual interest rate for your investment account is stored in cell B8, the number of monthly payments in cell B7, and the monthly payment amount in cell B6. Payments will be made at the beginning of every period. Pay attention to the time periods for the interest rate and payment schedule. Remember to express the Pmt argument as a negative.

1/1

In the Financial men Arguments dialog, y the Pv Rate Formula b6 in the Pv Pmt For clicked the OK butto

6 3

In cell B14, enter a formula using NPV to calculate the value today (the present value) of the tuition payment option 3. Use cell B7 as the Rate argument and the cell range B10:B13 as the Value1 argument. Use cell references for all values.

1/1

In the Financial men Arguments dialog, y Rate Formula Input, keydowned the Npv

6 4

In cell B8, enter a formula using NPER to calculate how many payments are left on this student loan. The annual interest rate is in cell B4. The monthly payment is in cell B7. The current value of the account is in cell B2. Payments will be made at the beginning of every period. Remember to adjust the interest rate to reflect the same time period as the payments and to express the Pmt argument as a negative.

1/1

In the Financial men Arguments dialog, y the Nper Rate Formu typed b2in the Nper Input, and keydowne

6 5

In cell B5, enter a formula using the function for the Declining balance depreciation method. Use the cell names Purchase, Sale, and Life for the function arguments. Use a relative reference to cell A5 for the Per argument. Enter a partial year of 4 months in the Month argument.

1/1

Inside the Function A typed Purchase in t Number2 Formula In typed a5 in the Db N Formula Input, and c

#

Question

Poin ts

Corre ct?

Submitted Answer

6 6

In cell B5, enter a formula using the function for the Straight-line depreciation method. Use the cell names Purchase, Sale, and Life for the function arguments.

1/1

In the Financial men Arguments dialog, y the Sln Number1 For Input, typed Life in t the OK button.

6 7

Enter a formula using DAVERAGE to calculate the average value in the Total Spent column for rows that meet the criteria in the criteria range A1:G2. The database is defined by the named range CustomersDB.

1/1

Inside the Insert Fun the Function Argume typed CustomersDB Spent in the Davera the Daverage Numbe Number3 Formula

6 8

Open the Evaluate Formula dialog.

1/1

In the Formulas Ribb Group, you clicked th

6 9

Enter a formula using the DCOUNTA function to count the number of cells in the Last Name column that meet the criteria in the criteria range A1:G2. The database is defined by the named range CustomersDB.

1/1

In the Formulas Ribb the Insert Function Arguments dialog, y

7 0

Edit the AVERAGEIFS formula in cell E2 using IFERROR so if the formula results in an error, the message not in PO will display instead of the #DIV/0! error. Edit the formula directly in the cell or the formula bar. Remember to use quotation marks around the text string.

1/1

You clicked cell E2, c "=IFERROR(AVERA not in PO")" in the f...