Enter a formula in cell D5 to calculate B5/B4 rounded to 4 decimal places.
answer
You typed =Ro in cell D5, double-clicked ROUND in the Formula AutoComplete list, typed =ROUND(B5/B4,4 in cell D5, and pressed Enter
question
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 Rents and Leases.
answer
You clicked cell B1, typed =SUMPRO in cell B1, double-clicked SUMPRODUCT in the Formula AutoComplete list, typed =SUMPRODUCT(Rents,Leases in cell B1, and pressed Enter.
question
Enter a formula in cell F2 using SUMIFS to calculate the total expense (use the named range Cost) where the value in the Category named range is equal to the text string "Office Expense" and the value in the SubCategory named range is equal to the text string "Parking".
answer
You typed =SUMIF(Category in cell F2. In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Logical button, clicked the Recently Used button, and clicked the Math & Trig button. In the Math & Trig menu, you clicked the SUMIFS menu item. Inside the Function Arguments dialog, you typed Cost in the Sum_range input, pressed the Tab key, typed Category in the Criteria_range1 input, pressed the Tab key, typed Office Expense in the Criteria1 input, pressed the Tab key, typed SubCategory in the Criteria_range2 input, pressed the Tab key, typed Parking in the Criteria2 input, and pressed the Enter key.
question
Enter a formula in the selected cell using SUMIF to calculate the total expenses for the category Office Expense. Use the range name Category for the Range argument, the text string "Office Expense" for the Criteria argument, and Cost for the Sum_range argument.
answer
You typed in cell F2. In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Math & Trig button. In the Math & Trig menu, you clicked the SUMIF menu item. Inside the Function Arguments dialog, you typed Category in the Range input, pressed the Tab key, typed Office Expense in the Criteria input, pressed the Tab key, typed Cost in the Sum_range input, and pressed the Enter key.
question
Enter a formula in cell F2 using AVERAGEIF to calculate the average cost for the category Computer Expense. Use the range name Category for the Range argument, the text string "Computer Expense" for the Criteria argument, and Cost for the Average_range argument.
answer
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Math & Trig button, clicked the Financial button, and clicked the More Functions button. In the More Functions menu in the Statistical menu, you clicked the AVERAGEIF menu item. Inside the Function Arguments dialog, you typed Category in the Range input, pressed the Tab key, typed Computer Expense in the Criteria input, pressed the Tab key, typed Cost in the Average_range input, and pressed the Enter key.
question
Enter a formula in the selected cell using AVERAGEIFS to calculate the average expense (use the named range Cost) where the value in the Category named range is equal to the text string "Computer Expense" and the value in the SubCategory named range is equal to the test string "Internet Access".
answer
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the More Functions button. In the More Functions menu in the Statistical menu, you clicked the AVERAGEIFS menu item. Inside the Function Arguments dialog, you typed Cost in the Average_range input, pressed the Tab key, typed Category in the Criteria_range1 input, pressed the Tab key, typed Computer Expense in the Criteria1 input, pressed the Tab key, typed SubCategory in the Criteria_range2 input, pressed the Tab key, typed Internet Access in the Criteria2 input, and pressed the Enter key.
question
In cell F2, enter a formula using COUNTIF to count the number of cells in the range named Cost that have a value less than 500.
answer
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the More Functions button. In the More Functions menu in the Statistical menu, you clicked the COUNTIF menu item. Inside the Function Arguments dialog, you typed Cost in the Range input, pressed the Tab key, typed <500 in the Criteria input, and pressed the Enter key.
question
In cell F2, enter a formula using COUNTIFS to count the number of rows where values in the range named Cost have a value less than 500 and cells in the range named Category have the value "Computer Expense".
answer
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the More Functions button. In the More Functions menu in the Statistical menu, you clicked the COUNTIFS menu item. Inside the Function Arguments dialog, you typed Cost in the Criteria_range1 input, pressed the Tab key, typed <500 in the Criteria1 input, pressed the Tab key, typed Category in the Criteria_range2 input, pressed the Tab key, typed Computer Expense in the Criteria2 input, and pressed the Enter key.
question
Enter a formula in cell G2 to find the middle value of the cell range D2:D14.
answer
You typed =Median in cell G2, double-clicked MEDIAN in the Formula AutoComplete list, selected the cell range D2:D14, and pressed Enter.
question
Enter a formula in cell E2 to calculate the absolute value of C2-D2.
answer
You typed in cell E2, clicked cell E2, clicked the E2 Cell Input, typed =ABS in cell E2, clicked the =ABS(C2-D2 view, double-clicked ABS in the Formula AutoComplete list, typed =ABS(C2-D2 in cell E2, and pressed Enter.
question
Enter an array formula in cells G1:G3 to display the three mode values from the range D2:D14. The results array has been selected for you.
answer
You typed =MOD in cell G1, clicked the undefined view, double-clicked MODE.MULT in the Formula AutoComplete list, typed =MODE.MULT(D2:D14 in cell G1, and pressed the Shift + Ctrl + Enter keyboard shortcut.
question
Enter a formula in cell G3 to find the single value that appears most often in the cell range D2:D14.
answer
You typed =MOD in cell G3, double-clicked MODE.SNGL in the Formula AutoComplete list, typed =MODE.SNGL(D2:D14 in cell G3, and pressed Enter
question
Enter a formula in cell F2 to find the averaged rank of the value in cell C4 compared to the values in cells C2:C13.
answer
You typed in cell F2, clicked the formula bar, typed "=RANK" in the formula bar, clicked the =RANK.AVG(C4,C2:C13 view, double-clicked RANK.AVG in the Formula AutoComplete list, double-clicked RANK.AVG in the Formula AutoComplete list, typed "=RANK.AVG(C4,C2:C13" in the formula bar, and pressed Enter.
question
Enter a formula in cell F1 to find the rank of the value in cell C4 compared to the values in cells C2:C13.
answer
You typed =RANK in cell F1, double-clicked RANK.EQ in the Formula AutoComplete list, typed =RANK.EQ(C4,C2:C13 in cell F1, and pressed Enter.
question
In cell A8, enter a formula using AND to display TRUE if sales in 2016 (cell B2) are greater than 1500000 (one million, five hundred thousand) and sales in 2017 (cell C2) are greater than 1500000 (one million, five hundred thousand). Use cell references where appropriate and enter the arguments exactly as described in this question.
answer
You clicked the formula bar, clicked cell B9, clicked cell B8, clicked cell A8, clicked the formula bar, clicked the formula bar, clicked the formula bar, typed "=AND(B2>1500000,C2>1500000" in the formula bar, and pressed Enter.
question
In cell A10, enter a formula using OR to display TRUE if net profit before tax in 2016 (cell B5) are greater than 750000 (seven hundred, fifty thousand) or net profit before tax in 2016 (cell C5) are greater than 750000 (seven hundred, fifty thousand). Use cell references where appropriate and enter the arguments exactly as described in this question.
answer
You typed =OR in cell A10, clicked the =OR(B5>750000,C5>750000 view, double-clicked OR in the Formula AutoComplete list, typed =OR(B5>750000,C5>750000 in cell A10, and pressed Enter.
question
In cell C5, enter a formula to calculate the future value of this investment. Use cell references wherever possible. The interest rate is stored in cell C4, the number of payments in cell C2, and the monthly investment amount in cell C3. Remember to use a negative value for the Pmt argument.
answer
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Financial button. In the Financial menu, you clicked the FV menu item. Inside the Function Arguments dialog, you typed C4/12 in the Rate input, pressed the Tab key, typed C2 in the Nper input, pressed the Tab key, typed -C3 in the Pmt input, and pressed the Enter key.
question
Enter a formula using PV in cell B6 to calculate the present value needed for this pension fund. Cell B3 is the expected annual interest rate. Cell B4 is the total number of monthly payments that will be made. Cell B2 is the amount of each monthly payment. Payments will be made at the beginning of every period. Remember to express the Pmt argument as a negative.
answer
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Financial button. In the Financial menu, you clicked the PV menu item. Inside the Function Arguments dialog, you typed B3/12 in the Rate input, pressed the Tab key, typed B4 in the Nper input, pressed the Tab key, typed -B2 in the Pmt input, pressed the Tab key, pressed Tab, pressed the Tab key, typed 1 in the Type input, and clicked the OK button.
question
In cell B9, enter a formula using NPV to calculate the present value of a payment plan with variable annual payments as shown in cells B11:B14. The interest rate is stored in cell B2. Use a cell range as a single Value argument.
answer
You clicked cell B9, typed in cell B9. In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Financial button. In the Financial menu, you clicked the NPV menu item. Inside the Function Arguments dialog, you typed B2 in the Rate input, pressed the Tab key, typed B11:B14 in the Value1 input, and pressed the Enter key.
question
In cell B10, enter a formula using NPER to calculate how many payments will be due on the loan if the monthly payment is increased to the amount in cell B8. Notice that the payment is already expressed as a negative value. The annual interest rate is in cell B3. The current value of the loan is in cell B2. Payments will be made at the beginning of every period.
answer
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Financial button. In the Financial menu, you clicked the NPER menu item. Inside the Function Arguments dialog, you typed B3/12 in the Rate input, pressed the Tab key, typed B8 in the Pmt input, pressed the Tab key, typed B2 in the Pv input, pressed the Tab key, pressed Tab, pressed the Tab key, typed 1 in the Type input, and pressed the Enter key.
question
In cell B7, enter a formula using the function for the Straight-line depreciation method. Use the cell names Cost and Salvage for the Cost and Salvage function arguments. Use the appropriate cell reference for the Life argument. Be sure to use the appropriate cell reference type to allow you to copy the formula to the rest of the column.
answer
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Insert Function button. Inside the Insert Function dialog, you clicked the Cancel button. In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Financial button. In the Financial menu, you clicked the SLN menu item. Inside the Function Arguments dialog, you typed Cost in the Cost input, pressed the Tab key, typed Salvage in the Salvage input, pressed the Tab key, typed $B$3 in the Life input, and pressed the Enter key.
question
In cell B7, enter a formula using the function for the Declining balance depreciation method. Use the cell names Cost and Salvage for the Cost and Salvage function arguments. Use the appropriate cell reference for the Life argument. Use the appropriate cell reference for the Period argument. Be sure to use the appropriate cell reference types to allow you to copy the formula to the rest of the column. The asset will be in use in year 1 for 5 months.
answer
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Financial button. In the Financial menu, you clicked the DB menu item. Inside the Function Arguments dialog, you typed Cost in the Cost input, pressed the Tab key, typed Salvage in the Salvage input, pressed the Tab key, typed $B$3 in the Life input, pressed the Tab key, typed A7 in the Period input, pressed the Tab key, typed 5 in the Month input, and pressed the Enter key.
question
Enter a nested function in cell F9 using INDEX and MATCH to find the ending balance for the date listed in cell C8. Use the named range Schedule to reference the cell range for the schedule of loan payments. The Ending Balance column is column 6. In the INDEX Row_num function argument, use MATCH to look up the row number for the date listed in C8. Use the named range PayDates to reference the cell range for the Payment Date column. Require an exact match.
answer
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Lookup & Reference button. In the Lookup & Reference menu, you clicked the INDEX menu item. Inside the Select Arguments dialog, you clicked the OK button. Inside the Function Arguments dialog, you typed Schedule in the Array input, pressed the Tab key, pressed Backspace, pressed the Tab key, and typed 6 in the Column_num input. You clicked the Name Box. In the Recently Used menu, you clicked the Match menu item. Inside the Function Arguments dialog, you typed C8 in the Lookup_value input, pressed the Tab key, typed PayDates in the Lookup_array input, pressed the Tab key, typed 0 in the Match_type input, and clicked the OK button.
question
In cell F8, enter a formula using the MATCH function to return the row number of the payment date listed in cell C8. Use the named range PayDates to reference the cell range for the Payment Date column. Require an exact match.
answer
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Lookup & Reference button. In the Lookup & Reference menu, you clicked the MATCH menu item. Inside the Function Arguments dialog, you typed C8 in the Lookup_value input, pressed the Tab key, typed PayDates in the Lookup_array input, pressed the Tab key, typed 0 in the Match_type input, and pressed the Enter key.
question
In cell F9, enter a formula using the INDEX function to return the ending loan balance for the row listed in cell F8. Use the named range Schedule to reference the cell range for the schedule of loan payments. The Ending Balance column is column 6.
answer
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Lookup & Reference button. In the Lookup & Reference menu, you clicked the INDEX menu item. Inside the Select Arguments dialog, you clicked the OK button. Inside the Function Arguments dialog, you typed Schedule in the Array input, pressed the Tab key, typed F8 in the Row_num input, pressed the Tab key, typed 6 in the Column_num input, and pressed the Enter key.
question
Enter a formula using a database function to calculate the total value in the Cost column for expenses that meet the criteria in the criteria range A2:E3. The database is defined by the named range Expenses.
answer
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Insert Function button. Inside the Insert Function dialog in the Or select a category drop-down, you selected Database. Inside the Insert Function dialog from the Select a function list, you selected DSUM. Inside the Insert Function dialog, you clicked the OK button. Inside the Function Arguments dialog, you typed Expenses in the Database input, pressed the Tab key, typed Cost in the Field input, pressed the Tab key, typed A2:E3 in the Criteria input, and pressed the Enter key.
question
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Insert Function button. Inside the Insert Function dialog in the Or select a category drop-down, you selected Database. Inside the Insert Function dialog from the Select a function list, you selected DSUM. Inside the Insert Function dialog, you clicked the OK button. Inside the Function Arguments dialog, you typed Expenses in the Database input, pressed the Tab key, typed Cost in the Field input, pressed the Tab key, typed A2:E3 in the Criteria input, and pressed the Enter key.
answer
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Insert Function button. Inside the Insert Function dialog in the Or select a category drop-down, you selected Database. Inside the Insert Function dialog from the Select a function list, you selected DCOUNTA. Inside the Insert Function dialog, you clicked the OK button. Inside the Function Arguments dialog, you typed Expenses in the Database input, pressed the Tab key, typed Billable? in the Field input, pressed the Tab key, typed A2:E3 in the Criteria input, and pressed the Enter key.
question
Enter a formula using a database function to calculate the average value in the Cost column for expenses that meet the criteria in the criteria range A2:E3. The database is defined by the named range Expenses.
answer
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Insert Function button. Inside the Insert Function dialog in the Or select a category drop-down, you selected Database. Inside the Insert Function dialog, you clicked the OK button. Inside the Function Arguments dialog, you typed Expenses in the Database input, pressed the Tab key, typed Cost in the Field input, pressed the Tab key, typed A2:E3 in the Criteria input, and pressed the Enter key.
question
Edit the MODE.SNGL formula in cell G3 using IFERROR so if the formula results in an error, the message none will display instead of the #N/A error. Edit the formula directly in the cell or the formula bar.
answer
You typed =IFErr in cell G3, clicked the =IFERROR(MODE.SNGL(D2:D14),"none" view, double-clicked MODE.SNGL in the Formula AutoComplete list, typed =IFERROR(Mod in cell G3, double-clicked MODE.SNGL in the Formula AutoComplete list, typed =IFERROR(MODE.SNGL(D2:D14),"none" in cell G3, and pressed Enter.
question
Open the Evaluate Formula dialog to analyze the formula in this cell.
answer
In the Formulas Ribbon Tab in the Formula Auditing Ribbon Group, you clicked the Evalute Formula button.
Haven't found what you need?
Search for quizzes and test answers now
Quizzes.studymoose.com uses cookies. By continuing you agree to our cookie policy
