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?
Signup and view all the answers
How do you create named ranges for the data table B8:E11 using the labels in row 1?
Signup and view all the answers
What action should be taken if there is an error in cell D6?
Signup and view all the answers
How can you display all formulas in the worksheet?
Signup and view all the answers
What should you do to hide the formulas and display their values in the worksheet?
Signup and view all the answers
How do you show tracer arrows from cell B5 to its dependent cells?
Signup and view all the answers
What is the method for showing tracer arrows from precedent cells to cell B5?
Signup and view all the answers
How can you hide all of the dependency tracer arrows at once?
Signup and view all the answers
How do you calculate the average value of cells B4:D4 in cell E4?
Signup and view all the answers
What formula should you enter in cell F12 to count numbers in the Ordered column?
Signup and view all the answers
How do you count the items in the Item column (C2:C11) in cell C12?
Signup and view all the answers
What formula do you enter in cell G12 to count blank cells in the Received column?
Signup and view all the answers
How do you find the lowest percentage of items received in the order in cell H12?
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?
Signup and view all the answers
How can you insert the current date in the selected cell without including the time?
Signup and view all the answers
What method do you use to insert the current date and time in the selected cell?
Signup and view all the answers
How do you calculate monthly payments for a loan in cell B7 using cell references?
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!