CS285Assign6 EnteraformulaincellD5tocalculateB5/B4roundedto4decimalplaces.- CORRECTANSWER-Youtyped=RoincellD5,double-clickedROUNDinthe FormulaAutoCompletelist,typed=ROUND(B5/B4,4incellD5,andpressedEnter EnteraformulaincellB1usingtheSUMPRODUCT functiontocalculatethetotal valueofthecurrentleasesbymultiplyingthecurrentmonthlyrentsbytheremaining monthsoneachlease.UsetherangenamesRentsandLeases.-CORRECT ANSWER-YouclickedcellB1,typed=SUMPROincellB1,double-clicked SUMPRODUCT intheFormulaAutoCompletelist,typed =SUMPRODUCT(Rents,Leases incellB1,andpressedEnter.EnteraformulaincellF2usingSUMIFStocalculatethetotalexpense(usethe namedrangeCost)wherethevalueintheCategorynamedrangeisequaltothetext string"OfficeExpense"andthevalueintheSubCategorynamedrangeisequalto thetextstring"Parking".-CORRECTANSWER-Youtyped=SUMIF(Categoryincell F2.IntheFormulasRibbonTabintheFunctionLibraryRibbonGroup,youclicked theLogicalbutton,clickedtheRecentlyUsedbutton,andclickedtheMath&Trig button.IntheMath&Trigmenu,youclickedtheSUMIFSmenuitem.Insidethe FunctionArgumentsdialog,youtypedCostintheSum_rangeinput,pressedtheTab key,typedCategoryintheCriteria_range1input,pressedtheTabkey,typedOffice ExpenseintheCriteria1input,pressedtheTabkey,typedSubCategoryinthe Criteria_range2input,pressedtheTabkey,typedParkingintheCriteria2input,and pressedtheEnterkey.EnteraformulaintheselectedcellusingSUMIFtocalculatethetotalexpensesfor thecategoryOfficeExpense.UsetherangenameCategoryfortheRange argument,thetextstring"OfficeExpense"fortheCriteriaargument,andCostforthe Sum_rangeargument.-CORRECTANSWER-YoutypedincellF2.IntheFormulas RibbonTabintheFunctionLibraryRibbonGroup,youclickedtheMath&Trig button.IntheMath&Trigmenu,youclickedtheSUMIFmenuitem.Insidethe FunctionArgumentsdialog,youtypedCategoryintheRangeinput,pressedtheTab key,typedOfficeExpenseintheCriteriainput,pressedtheTabkey,typedCostin theSum_rangeinput,andpressedtheEnterkey.EnteraformulaincellF2usingAVERAGEIFtocalculatetheaveragecostforthe categoryComputerExpense.UsetherangenameCategoryfortheRange argument,thetextstring"ComputerExpense"fortheCriteriaargument,andCostfor theAverage_rangeargument.-CORRECTANSWER-IntheFormulasRibbonTabin theFunctionLibraryRibbonGroup,youclickedtheMath&Trigbutton,clickedthe Financialbutton,andclickedtheMoreFunctionsbutton.IntheMoreFunctionsmenu intheStatisticalmenu,youclickedtheAVERAGEIFmenuitem.InsidetheFunction Argumentsdialog,youtypedCategoryintheRangeinput,pressedtheTabkey, 1 / 2
typedComputerExpenseintheCriteriainput,pressedtheTabkey,typedCostinthe Average_rangeinput,andpressedtheEnterkey.EnteraformulaintheselectedcellusingAVERAGEIFStocalculatetheaverage expense(usethenamedrangeCost)wherethevalueintheCategorynamedrange isequaltothetextstring"ComputerExpense"andthevalueintheSubCategory namedrangeisequaltotheteststring"InternetAccess".-CORRECTANSWER-In theFormulasRibbonTabintheFunctionLibraryRibbonGroup,youclickedtheMore Functionsbutton.IntheMoreFunctionsmenuintheStatisticalmenu,youclicked theAVERAGEIFSmenuitem.InsidetheFunctionArgumentsdialog,youtypedCost intheAverage_rangeinput,pressedtheTabkey,typedCategoryinthe Criteria_range1input,pressedtheTabkey,typedComputerExpenseintheCriteria1 input,pressedtheTabkey,typedSubCategoryintheCriteria_range2input,pressed theTabkey,typedInternetAccessintheCriteria2input,andpressedtheEnterkey.IncellF2,enteraformulausingCOUNTIFtocountthenumberofcellsintherange namedCostthathaveavaluelessthan500.-CORRECTANSWER-IntheFormulas RibbonTabintheFunctionLibraryRibbonGroup,youclickedtheMoreFunctions button.IntheMoreFunctionsmenuintheStatisticalmenu,youclickedthe COUNTIFmenuitem.InsidetheFunctionArgumentsdialog,youtypedCostinthe Rangeinput,pressedtheTabkey,typed<500intheCriteriainput,andpressedthe Enterkey.IncellF2,enteraformulausingCOUNTIFStocountthenumberofrowswhere valuesintherangenamedCosthaveavaluelessthan500andcellsintherange namedCategoryhavethevalue"ComputerExpense".-CORRECTANSWER-Inthe FormulasRibbonTabintheFunctionLibraryRibbonGroup,youclickedtheMore Functionsbutton.IntheMoreFunctionsmenuintheStatisticalmenu,youclicked theCOUNTIFSmenuitem.InsidetheFunctionArgumentsdialog,youtypedCostin theCriteria_range1input,pressedtheTabkey,typed<500intheCriteria1input, pressedtheTabkey,typedCategoryintheCriteria_range2input,pressedtheTab key,typedComputerExpenseintheCriteria2input,andpressedtheEnterkey.
EnteraformulaincellG2tofindthemiddlevalueofthecellrangeD2:D14.-
CORRECTANSWER-Youtyped=MedianincellG2,double-clickedMEDIANinthe
FormulaAutoCompletelist,selectedthecellrangeD2:D14,andpressedEnter.
EnteraformulaincellE2tocalculatetheabsolutevalueofC2-D2.-CORRECT ANSWER-YoutypedincellE2,clickedcellE2,clickedtheE2CellInput,typed=ABS incellE2,clickedthe=ABS(C2-D2view,double-clickedABSintheFormula AutoCompletelist,typed=ABS(C2-D2incellE2,andpressedEnter.
EnteranarrayformulaincellsG1:G3todisplaythethreemodevaluesfromthe
rangeD2:D14.Theresultsarrayhasbeenselectedforyou.-CORRECT
- / 2