Excel study set 2

Enter a formula in cell C2 to extract the two digit day from the sales date in cell A2. The day number begins with the third character in the text string. Use cell references where appropriate.

:) Formulas Text Left Type c4 in first Type 2 in second Ok

In cell E7, enter a formula using the function for the Double-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. Use a multiplier of 3.

:) Select cell Financial button DDB Cost salvage life A8 1.5

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.

Click cell B1, Type =SUMPRODUCT(Rents,Leases in cell B1, and pressed enter

Enter a formula in cell E2 to calculate the absolute value of C2-D2.

Click cell E2 =ABS(C2-D2) then press enter

Enter a formula using a database function to count the number of cells in the Billable? column for expenses that meet the criteria in the criteria range A2:E3. The database is defined by the named range Expenses.

Expenses :))) "Billable" A2:E3

In cell C7, enter a formula using the function for the Accelerated, or sum-of-the-years' digits (SOYD), 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 Per argument. Be sure to use the appropriate cell reference types to allow you to copy the formula to the rest of the column.

Financial Syd Cost Salvage Life Year 1

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.

Formula Financial PV B3/12 B4 in Nper -B2 in pmt Press tab key 3x Type 1 in input Ok

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.

Formula Financial SLN Cost Salvage $B$3 in life Ok

Enter a formula in cell D2 to find the four digit year by extracting the last four digits of the sale date in cell A2. Use cell references where appropriate.

Formula Text Right A2 in text 4 in num_charts Ok

In cell D7, 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.

Formulas Financial DB Cost Salvage $D$3 in life input A7 in period 5 in month (NOT Sure about this 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.

Formulas Lookup and reference INDEX Select ok Schedule in array 6 in column_num Name box Click match C8 in lookup_value PayDates in lookup array 0 in matchtype

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

Formulas Lookup reference Match C8 in lookup value Paydates in lookup array 0 in match type click ok

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.

Formulas Financial button. NPER you typed B3/12 in the Rate input, typed B8 in the Pmt input typed B2 in the Pv input, typed 1 in the Type input, and pressed the Enter key.

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.

Formulas Insert Function button Select a category drop-down, you selected Database. DSUM. OK button you typed Expenses in the Database input Cost in the Field input typed A2:E3 in the Criteria input, and pressed the Enter key.

Enter a formula in cell D5 to calculate B5/B4 rounded up to 4 decimal places.

Formulas Ribbon Math & Trig button ROUNDUP B5/B4 typed 4 in the Num_digits input, clicked the OK button.

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.

Formulas tab Insert function Select data base from drop down arrow Select DSUM Inside function arguments click ok Expenses in database input Cost in field ' A2:A3 in criteria

Enter formula in cell B9 using the IFS function to calculate the owner's draw from the company. If the value in cell B7 is greater than or equal to 500000, the draw amount is 50000. If the value in cell B7 is greater than or equal to 150000, the bonus is 5000. If the value in cell B7 is less than 150000, display the text "no draw".

Formulas tab Logical IFS B7>=500000 in the Logical_test1 input 50000 in the Value_if_true1 input, B7>=150000 in the Logical_test2 5000 in the Value_if_true2 input, B7<150000 in the Logical_test3 input, type no draw in the Value_if_true3 input, and press Enter

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.

In f2 you clicked formulas Math and trig SUMIF Category in range input Office Expenses in criteria Cost in Sum_range Ok

Open the Evaluate Formula dialog to analyze the formula in this cell.

In the Formulas Ribbon Tab in the Formula Auditing Ribbon Group, you clicked the Evaluate Formula button

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.

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, typed C2 in the Nper input, typed -C3 in the Pmt input, and clicked the OK button.

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.

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, typed B11:B14 in the Value1 input, and clicked the OK button.

In cell A8, enter a formula using AND to display TRUE if sales in 2019 (cell B2) are greater than 1500000 (one million, five hundred thousand) and sales in 2020 (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.

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Logical button. In the Logical menu, you clicked the AND menu item. Inside the Function Arguments dialog, you typed B2>1500000 in the Logical1 input, typed C2>1500000 in the Logical2 input, and clicked the OK button.

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 STDEV.P menu item. Inside the Function Arguments dialog, you typed D2:D13 in the Number1 input, clicked the OK button.

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Logical button. In the Logical menu, you clicked the OR menu item. Inside the Function Arguments dialog, you typed B5>750000 in the Logical1 input, typed C5>750000 in the Logical2 input, and clicked the OK button.

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.

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, typed F8 in the Row_num input, typed 6 in the Column_num input, and clicked the OK button.

Enter a formula in cell D5 to calculate B5/B4 rounded to 4 decimal places.

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 ROUND menu item. Inside the Function Arguments dialog, you typed B5/B4 in the Number input, typed 4 in the Num_digits input, and clicked the OK button.

Enter a formula in cell D5 to calculate B5/B4 rounded down to 4 decimal places. 1/1

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 ROUNDDOWN menu item. Inside the Function Arguments dialog, you typed B5/B4 in the Number input, typed 4 in the Num_digits input, and clicked the OK button.

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".

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 SUMIFS menu item. Inside the Function Arguments dialog, you typed Cost in the Sum_range input, typed Category in the Criteria_range1 input, typed "Office Expense" in the Criteria1 input, typed SubCategory in the Criteria_range2 input, typed "Parking" in the Criteria2 input, and clicked the OK button.

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.

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 AVERAGEIF menu item. Inside the Function Arguments dialog, you typed Category in the Range input, typed "Computer Expense" in the Criteria input, typed Cost in the Average_range input, and clicked the OK button.

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".

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, typed Category in the Criteria_range1 input, typed "Computer Expense" in the Criteria1 input, typed SubCategory in the Criteria_range2 input, typed "Internet Access" in the Criteria2 input, and clicked the OK button.

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.

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 "<500" in the Criteria input, typed Cost in the Range input, and clicked the OK button.

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".

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, typed "<500" in the Criteria1 input, typed Category in the Criteria_range2 input, typed "Computer Expense" in the Criteria2 input, and clicked the OK button.

Enter a formula in cell F1 to find the rank of the value in cell C4 compared to the values in cells C2:C13.

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 RANK EQ menu item. Inside the Function Arguments dialog, you typed C4 in the Number input, typed C2:C13 in the Ref input, and clicked the OK button.

Enter a formula in cell G2 to calculate the standard deviation of the values in cells D2:D13. This array of values is the entire data set. 1/1

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 STDEV.P menu item. Inside the Function Arguments dialog, you typed D2:D13 in the Number1 input, clicked the OK button.

Enter a formula in cell G1 to calculate the standard deviation of the values in cells D2:D13. Assume this array is a sample of a larger set of values.

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 STDEV.S menu item. Inside the Function Arguments dialog, you typed D2:D13 in the Number1 input, clicked the DialogHeader view, clicked the dialog undefined button, and clicked the OK button.

Enter a formula in cell B2 to find the month number by extracting the first two letters of the sales date in cell A2. Use cell references where appropriate.

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Text button. In the Text menu, you clicked the LEFT menu item. Inside the Function Arguments dialog, you typed A2 in the Text input, typed 2 in the Num_chars input, and clicked the OK button.

Enter a formula in cell H1 to calculate the mean of the differences in cells E2:E13.

You clicked the formula bar, typed "=AVERAGE(E2:E13)" in the formula bar, and pressed Enter.

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.

You clicked the formula bar, typed "=IFERROR(MODE.SNGL(D2:D14)" in the formula bar, clicked the formula bar, typed "=IFERROR(MODE.SNGL(D2:D14),"none")" in the formula bar, and pressed Enter.

Enter a formula in cell G2 to find the middle value of the cell range D2:D14.

You clicked the formula bar, typed "=MEDIAN(D2:D14)" in the formula bar, and pressed Enter.

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.

You clicked the formula bar, typed "=MODE.MULT(D2:D14)" in the formula bar, and pressed the Shift + Ctrl + Enter keyboard shortcut.

Enter a formula in cell G3 to find the single value that appears most often in the cell range D2:D14.

You typed =MODE.SNGL(D2:D14) in cell G3, and pressed Enter

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.

You typed in cell F2, then in formula bar "=RANK.AVG(C4,C2:C13) in the formula bar, and pressed Enter.