Excel for Accounting - Study Guide
What is the approximate decimal representation of 15 minutes in Excel?
.01
Cell H9 contains the date and time 1/18/23 19:42, and cell H10 contains 1/20/23 2:22. What is the approximate result of H10 - H9 in number format?
1.27
In the formula = PMT(0.07/26,15*26,1000), the annual interest rate is 7 percent. What is the duration of the loan?
15 years
What does = COUNTIFS(A:A, 1, C:C, 3) return for the data in this image?
2
Column C contains the values 2, 5, 7, 3, 4, 2, 1, and 2. What does = COUNTIF(C:C, 2) return?
3
If F4 contains 3, B4 contains "TRUE", and C4 contains 7, what does =IF(ISBLANK(F4), 0, IF(B4 = "FALSE", F4, C4)) return?
7
What does = XLOOKUP(B5, A:A, C:C) return for the table in this image?
7
You have a start time, ST; an end time, ET; and an hourly rate, HR. Which formula correctly calculates the expense?
= (ET-ST)*HR*24
Which formula exclusively uses relative referencing?
= G7
Which formula returns NEGATIVE when the entry C2 is less than 0?
= IF(C2 < 0, "NEGATIVE", "FAIL")
You want to calculate the monthly payment for a 30-year mortgage of $480,000 at 5.4 percent annual interest. Which formula performs the desired calculation?
= PMT(5.4/12, 30*12, 480000)
What does =IF(C47 = B22, "Cyan", "Azure") return if C47 contains 12 and B22 contains 5?
Azure
How should you check for errors in a spreadsheet?
Calculate quantities with multiple strategies.
Why should you use XLOOKUP instead of VLOOKUP?
It is simpler, more robust, and more versatile.
You need to use the results of a PivotTable in additional calculations. How should you proceed?
Recalculate the data using functions such as SUMIF and COUNTIF.
How can copying a single formula to several different cells produce different results for each cell?
Relative referencing can specify different input data.
What is wrong with the syntax in =XLOOKUP(B4, A, J)?
The second two arguments should be arrays like A:A
Why would you typically use the XLOOKUP function?
to import specific information from one table into another
Why should you use cell references instead of typing in values?
to reduce work and errors
Why would you use mixed referencing?
to specify one type of referencing for the row and another for the column
When should you use absolute referencing?
when the input data does not depend on the output cell
You type the formula = SUMIF($A:$A,"WORK", F:F) in cell M1. How does the formula function if you copy it to cell O1?
= SUMIF($A:$A, "WORK", H:H)
You see the number 48193 in a cell and suspect that it represents a date. How can you display it as a date?
Adjust the format of the cell.
How do you modify the organization of a PivotTable?
Change Pivot fields
For the formula = SUMIF(E:E, "Y", D:D), which column must satisfy the condition in order to sum that row?
E
One of your error checking calculations indicates a discrepancy. What should be your next step?
Examine the amount of the discrepancy.
You are interested in exploring the best chart types for a given data set. What options should you choose after selecting the data?
Insert > Recommended Charts
Why should you exercise caution when using the TODAY() function?
It produces different results depending on the actual day.
Which column does the formula = SUMIFS(J:J, B:B:, "J", A:A, "F", P:P, "H") sum?
J
You created a chart and are ready to move it to another sheet. How should you proceed?
Right-click on the chart and select Move Chart.
How can you easily convert a date with a time to just a date?
Round and truncate
How do you toggle between different types of referencing?
Select F4 or Cmd+T
How do you add a PivotTable?
Select the data, and then select Insert > PivotTable.
You have a large table from which you are interested in building a summary chart. What should be your first step?
Select the relevant data.
In addition to handling multiple conditions, how does SUMIFS differ from SUMIF?
The values to sum are the first argument for SUMIFS and the last for SUMIF.
How can you quickly reveal hidden rows and columns?
Use select All, and then right-click and select Unhide
Why is it important to understand VLOOKUP if XLOOKUP replaced it?
VLOOKUP is still present in legacy workbooks.
Which type of referencing occurs in the formula, = $F$12/$G$1 + $M$2?
absolute
Where should you perform and display the results of error checking?
in a central and separate location
You have a table of data that uses several formulas to compute column values. Where should you insert a new row?
in the middle of the table
What is the third argument of the SUMIF function?
the value to be tallied
Why would you use the NPER function?
to calculate the number of payments required to pay off a loan
