BUSI 201 Test 1 Update A+ Grade

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. 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. 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". Enter a formula in cell E2 to calculate the median value of the prices in the cell range C6:C14. 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. 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". 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. Enter a formula in cell C1 to find the rank of the value in cell C9 compared to the values in cells C5:C13. 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) or 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. 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.

Apply conditional formatting so cells with a value greater than the average are formatted using a yellow fill with dark yellow text. 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. Remove the fill color from the selected cells. Apply the Calculation cell style to the selected cells. Use Format Painter to copy the formatting from cell E1 and apply it to cell F1. Set the print area. 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). Copy the Car Loan worksheet to a new workbook. Unhide column E. Switch to Page Break Preview view. Modify the PO Q4 worksheet so gridlines will print. Change the scaling option so all columns will print on one page. Group together the Q1-Q2 and Q3-Q4 worksheets. Autofit column F to best fit the data. Freeze the top row of the worksheet. 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. 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. Open the Evaluate Formula dialog. 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.

Edit the Bonuses named range so it refers to cells B2:B5 on the Bonus worksheet. Close the Name Manager when you are finished. 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. Display the formulas in this worksheet. Enter a formula in cell D2 to calculate C2/C14 rounded to 3 decimal places. Enter a formula in cell C3 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. 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". 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. Enter a formula in cell B2 using the VLOOKUP function to find the total sales for the date in cell B1. Use the name DailySales for the lookup table. The total sales are located in column 5 of the lookup table. Be sure to require an exact match. 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).

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. Modify the worksheet so the first row is always visible when you scroll the worksheet down. Apply the date number format to the selected cells to display dates in the format similar to 14-Mar. Use AutoFill to complete the series from cell B4 through cell D4. Edit the formula in cell B9 so the references to cell E2 will update when the formula is copied, and the reference to cell B8 will remain constant. Use AutoFill to copy the formula to cells B10:B12. Click the Name Box. Apply the Accounting Number Format to the selected cells. Apply the Short Date format similar to 7/1/2016 to the selected cells. Use AutoSum to enter a SUM function in the selected cell. Create a new file based on the Personal expenses calculator template. Apply conditional formatting to the selected cells using the blue solid fill data bar. Apply conditional formatting to the selected cells using the Three Symbols (Circled) icon set (the first icon set in the Indicators section). 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. 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. In cell B6, enter a formula to calculate the future value of this savings strategy. Use cell references wherever 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.

Set row 1 to print on every page. Enter a formula in cell E4 to calculate the average value of cells B4:D4. In cell H12, enter a formula to find the highest percentage of items received in the order (cells H2:H11). In cell C12, enter a formula using a counting function to count the number of items in the Item column (cells C2:C11). In cell G12, enter a formula using a counting function to count the number of blank cells in the Received column (cells G2:G11). In cell H12, enter a formula to find the lowest percentage of items received in the order (cells H2:H11). Insert the current date and time in the selected cell. Insert the current date in the selected cell. Do not include the current time. In cell F12, enter a formula using a counting function to count numbers in the Ordered column (cells F2:F11). Enter a formula in cell B8 to display the text from cell A8 in all lower case letters. Name cell B1 as follows: BonusRate Use the Create from Selection command to create named ranges for the data table B8:E11 using the labels in row 1 as the basis for the names. 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). 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. Enter a formula in cell B8 to display the text from cell A8 in all upper case letters. On the Summary sheet, in cell B3, enter a formula to display the value of cell B3 from the ByMonth sheet. 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. 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.