CIS 150 Practice 07: Excel Chapter 2

Questions and Answers

What formula should be entered in the selected cell to calculate the value of cell E9 times 3?

=E9*3

How can you display the value of cell B3 from the ByMonth sheet in cell B3 on the Summary sheet?

= and then click the ByMonth sheet tab, click cell B3

How do you edit the formula in cell B9 to update a reference to cell E2 and keep B8 constant?

=E2*$B$8

What formula would you enter to display the value of cell E3?

=E3 Signup and view all the answers

How do you create named ranges for the data table B8:E11 using the labels in row 1?

Click the Create from Selection button after selecting the top row. Signup and view all the answers

What action should be taken if there is an error in cell D6?

Click cell D6 and select Copy Formula from Above. Signup and view all the answers

How can you display all formulas in the worksheet?

Click the Show Formulas button on the Formulas tab. Signup and view all the answers

What should you do to hide the formulas and display their values in the worksheet?

Click the Show Formulas button on the Formulas tab to hide the formulas. Signup and view all the answers

How do you show tracer arrows from cell B5 to its dependent cells?

Click the Trace Dependents button on the Formulas tab. Signup and view all the answers

What is the method for showing tracer arrows from precedent cells to cell B5?

Click the Trace Precedents button on the Formulas tab. Signup and view all the answers

How can you hide all of the dependency tracer arrows at once?

Click the Remove Arrows button on the Formulas tab. Signup and view all the answers

How do you calculate the average value of cells B4:D4 in cell E4?

Select Average from the AutoSum button options. Signup and view all the answers

What formula should you enter in cell F12 to count numbers in the Ordered column?

Select COUNT from the More Functions menu and apply it to cells F2:F11. Signup and view all the answers

How do you count the items in the Item column (C2:C11) in cell C12?

Select COUNTA from the More Functions options. Signup and view all the answers

What formula do you enter in cell G12 to count blank cells in the Received column?

Select COUNTBLANK from the More Functions options. Signup and view all the answers

How do you find the lowest percentage of items received in the order in cell H12?

Select Min from the AutoSum options. Signup and view all the answers

What action do you take to find the highest percentage of items received in the order in cell H12?

Select Max from the AutoSum options. Signup and view all the answers

How can you insert the current date in the selected cell without including the time?

Click the TODAY option under the Date & Time button. Signup and view all the answers

What method do you use to insert the current date and time in the selected cell?

Click the NOW option under the Date & Time button. Signup and view all the answers

How do you calculate monthly payments for a loan in cell B7 using cell references?

Use a negative value for the Pv argument. Signup and view all the answers

Study Notes

Excel Formulas and Functions

To calculate the value of cell E9 multiplied by 3, use the formula =E9*3 and press Enter.
To display the value from cell B3 on the ByMonth sheet in cell B3 of the Summary sheet, type = then select ByMonth sheet and click on cell B3, then press Enter.
For editing cell B9 to update references correctly, change the formula to =E2*$B$8 to make B8 constant while copying it down to B10:B12 using AutoFill.

Data Manipulation

To display the value of cell E3, simply enter the formula =E3 and press Enter.
Use the Create from Selection command to name ranges based on labels in row 1; click on the Formulas tab, select Create from Selection, ensure the Top row checkbox is checked, and click OK.

Error Correction

In case of an error in cell D6, click on the Smart Tag and choose "Copy Formula from Above" to accept Excel's fix suggestion.

Formula Display and Tracing

To show all formulas in the worksheet, navigate to the Formulas tab and click the Show Formulas button.
To hide formulas and display values, click the Show Formulas button again in the Formulas tab.
To display tracer arrows showing dependents of cell B5, click the Trace Dependents button in the Formulas tab.
Trace the precedents of cell B5 by selecting the Trace Precedents button in the same section.
To remove all dependency tracer arrows at once, click the Remove Arrows button in the Formulas tab.

Statistical Functions

To calculate the average of cells B4:D4, use the AutoSum button arrow on the Home tab and select Average before pressing Enter.
Count the numbers in the Ordered column (F2:F11) using the COUNT function: select the range and click OK.
Use COUNTA function to count items in the Item column (C2:C11) by selecting the range after choosing More Functions under Statistical.
To determine blank cells in the Received column (G2:G11), employ the COUNTBLANK function similarly.
Use the AutoSum arrow to find the minimum and maximum values in H2:H11 by selecting Min or Max in cell H12, then pressing Enter.

Date and Time Functions

Insert the current date without time using the TODAY function found in the Date & Time button under the Formulas tab.
For the current date and time, use the NOW function from the same Date & Time button.

Financial Functions

To calculate monthly loan payments in cell B7, incorporate cell references with a negative value for the Present Value (Pv) argument using the Financial button in the Formulas tab.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

Test your knowledge of Excel formulas with this quiz based on Chapter 2 of the CIS 150 course. Each flashcard presents practical tasks and definitions related to effective spreadsheet calculations. Perfect for mastering your Excel skills!