50 multiple-choice questions with answers and detailed explanations. Untimed — choose an answer to check it instantly.
0/50 answered0 correct
Question 1 of 50
Which Excel function calculates the monthly payment for a loan based on constant payments and a constant interest rate?
Correct answer: B) =PMT()
Explanation: Explanation: The =PMT() function calculates the payment for a loan based on constant payments and a constant interest rate.
Question 2 of 50
Which formula will correctly calculate the difference between the total amount paid over a loan and the original loan amount?
Correct answer: B) =G12-ABS(C11)
Explanation: Explanation: This formula calculates the total interest paid by subtracting the absolute value of the original loan amount from the total amount paid over the course of the loan.
Question 3 of 50
Which function will provide the present value of an investment when given the interest rate, number of periods, and payment?
Correct answer: C) =PV()
Explanation: Explanation: The =PV() function calculates the present value of an investment based on constant periodic payments and a constant interest rate.
Question 4 of 50
Which two tools are found in the Review tab of Excel and can be used for protecting and managing changes in a worksheet?
Correct answer: C) Track Changes and Protect Sheet
Explanation: Explanation: The Review tab allows users to protect a sheet or workbook and track any changes made by collaborators.
Question 5 of 50
A student is calculating the correlation between study hours and exam scores using Excel. Which function should they use?
Correct answer: A) =CORREL()
Explanation: Explanation: The =CORREL() function calculates the correlation coefficient between two datasets, such as study hours and exam scores.
Question 6 of 50
Which Excel feature will allow you to display the number $3.10 in a cell?
Correct answer: A) Select the cell and format it as Currency, then enter 3.1
Explanation: Explanation: By formatting the cell as Currency, Excel will automatically display the value as $3.10 when 3.1 is entered.
Question 7 of 50
Which of the following functions will count the total number of employees in a table who worked more than 40 hours?
Correct answer: A) =COUNTIF()
Explanation: Explanation: The =COUNTIF() function counts the number of cells that meet a specific condition, such as employees who worked more than 40 hours.
Question 8 of 50
Which formula will calculate the principal amount for the first period in an amortization schedule?
Correct answer: B) =C20-D20
Explanation: Explanation: The principal for the first period is calculated by subtracting the interest amount (D20) from the total payment (C20).
Question 9 of 50
Which Excel function allows you to automatically populate the "Hourly Wage" column by looking up the employee name in a reference table?
Correct answer: B) =VLOOKUP()
Explanation: Explanation: The =VLOOKUP() function searches for a value vertically down the first column of a reference table and returns the corresponding value from a specified column.
Question 10 of 50
Which formula correctly calculates the payment for a loan, assuming the rate is in cell C13, the term is in C12, and the loan amount is in C11?
Correct answer: B) =PMT(C13/12,C12,C11)
Explanation: Explanation: To calculate the monthly payment, divide the annual interest rate (C13) by 12 and use it in the PMT formula.
Question 11 of 50
A manager wants to determine how much money a customer will have after saving $100 per month for two years at a 5% interest rate. Which function should they use?
Correct answer: B) =FV()
Explanation: Explanation: The =FV() function calculates the future value of an investment based on regular, periodic payments and a constant interest rate.
Question 12 of 50
A doctor tracks patient diagnoses in a report and wants to filter the data to show only patients diagnosed with a specific illness. What tool should they use?
Correct answer: A) Filter
Explanation: Explanation: The Filter tool allows users to display only the rows that meet certain criteria, such as a specific diagnosis.
Question 13 of 50
Which Excel formula will calculate the interest amount for the first period of a loan if the balance in period 0 is in cell F19 and the loan interest rate is in cell C13?
Correct answer: A) =F19*C13/12
Explanation: Explanation: To calculate the interest for a period, multiply the balance in F19 by the annual interest rate (C13) divided by 12.
Question 14 of 50
A grocery store wants to track how many products sold at least 500 units. Which function will accomplish this?
Correct answer: A) =COUNTIF(C1:C6,">=500")
Explanation: Explanation: The =COUNTIF() function counts the number of cells in the specified range that meet the condition of being greater than or equal to 500.
Question 15 of 50
An analyst is calculating the sales forecast for each product if expected attendance is 18,000 people. Which function should they use?
Correct answer: B) =FORECAST()
Explanation: Explanation: The =FORECAST() function predicts future values based on known values, making it ideal for sales forecasts.
Question 16 of 50
Which function will count the number of responses in a survey that contain an age, where non-responses are marked with an "N"?
Correct answer: C) =COUNT()
Explanation: Explanation: The =COUNT() function counts numeric entries, such as ages, while ignoring non-numeric values like "N."
Question 17 of 50
A restaurant tracks daily sales. Which formula will return the total sales for days that exceeded $10,000?
Correct answer: B) =SUMIF(A1:A7,">10000")
Explanation: Explanation: The =SUMIF() function adds up values in a range that meet a specified condition, such as sales greater than $10,000.
Question 18 of 50
Which Excel feature should you use to prevent users from entering invalid data in a specific cell?
Correct answer: B) Data Validation
Explanation: Explanation: Data Validation restricts the type of data or values that users can enter into a specific cell or range.
Question 19 of 50
Which formula will return the text "On Track" if the number of appointments in cell B2 is greater than or equal to 10 and "Off Track" if it is less than 10?
Correct answer: A) =IF(B2>=10,"On Track","Off Track")
Explanation: Explanation: The =IF() function checks if the number of appointments in B2 is greater than or equal to 10 and returns "On Track" if true and "Off Track" if false.
Question 20 of 50
Which Excel function is used to calculate the total amount of sales, where the quantity in each row is greater than zero?
Correct answer: B) =SUMIF()
Explanation: Explanation: The =SUMIF() function adds up values in a specified range based on a given condition, such as sales with a quantity greater than zero.
Question 21 of 50
A spreadsheet tracks loan payments. Which formula would you use to calculate the number of monthly payments required to pay off a loan of $10,000 at $200 per month over five years?
Correct answer: B) =NPER()
Explanation: Explanation: The =NPER() function calculates the number of periods needed to pay off a loan or investment, based on constant payments and a constant interest rate.
Question 22 of 50
Which function will provide the total interest paid over the course of a loan, based on a list of monthly interest payments?
Correct answer: C) =SUM()
Explanation: Explanation: The =SUM() function will add up all the interest payments made over the duration of the loan.
Question 23 of 50
A sales manager wants to know which formulas reference a specific cell, such as B2. Which tool should they use?
Correct answer: A) Trace Precedents
Explanation: Explanation: The Trace Precedents tool shows which formulas reference a specific cell.
Question 24 of 50
Which formula would be used to find the average sales for the days when sales exceeded $10,000?
Correct answer: B) =AVERAGEIF(B1:B7,">10000")
Explanation: Explanation: The =AVERAGEIF() function calculates the average of cells that meet a specific condition, such as sales greater than $10,000.
Question 25 of 50
Which formula will calculate the salary expenses based on the number of employees and their individual salaries?
Correct answer: C) =SUM(A1:A5*B1:B5)
Explanation: Explanation: This formula multiplies the number of employees by their respective salaries and sums the results.
Question 26 of 50
An analyst needs to determine how much more food sales were generated at concerts compared to other events. Which formula should they use?
Correct answer: A) =SUM(Food_Concert)-SUM(Food_Other)
Explanation: Explanation: The formula calculates the difference in food sales by subtracting the total sales from other events from the total sales at concerts.
Question 27 of 50
Which formula should be used to calculate the total sales for the 4th quarter of the year, with monthly sales in cells B2, B3, and B4?
Correct answer: C) =SUM(B2,B3,B4)
Explanation: Explanation: All three formulas will correctly sum the sales in B2, B3, and B4. The =SUM() function is more commonly used for readability.
Question 28 of 50
An employee tracks the correlation between the number of hours worked and job performance. The correlation coefficient is 0.5. What does this suggest?
Correct answer: C) Moderate correlation
Explanation: Explanation: A correlation coefficient of 0.5 indicates a moderate relationship between the two variables.
Question 29 of 50
A business tracks sales for multiple products. Which formula will return the minimum sales for a given range?
Correct answer: B) =MIN()
Explanation: Explanation: The =MIN() function returns the smallest value from a specified range of cells.
Question 30 of 50
An Excel model uses multiple inputs that change frequently. Which feature is useful for evaluating different scenarios within the model?
Correct answer: C) Scenario Manager
Explanation: Explanation: Scenario Manager allows users to test different sets of inputs in a model and compare the results.
Question 31 of 50
A client wants to borrow $10,000 with monthly payments of $250. Which function will calculate the maximum annual interest rate that satisfies this payment schedule?
Correct answer: B) =RATE()
Explanation: Explanation: The =RATE() function calculates the interest rate required to meet the payment schedule, given the loan amount and number of periods.
Question 32 of 50
A table contains data for sales reps and their total sales. Which formula will sum the sales for a specific rep, such as Rep B?
Correct answer: A) =SUMIF(A1:A5,"Rep B",B1:B5)
Explanation: Explanation: The =SUMIF() function adds the sales values in B1 where the corresponding rep in A1 matches "Rep B."
Question 33 of 50
A bank offers several loan interest rate options. Which Excel tool would allow a user to compare the total monthly payments across all options?
Correct answer: B) Scenario Manager
Explanation: Explanation: Scenario Manager allows users to compare multiple scenarios (such as different interest rates) and see how the changes affect the total payments.
Question 34 of 50
Which formula will display "Y" for sales greater than or equal to $3,000 and "N" for sales below $3,000?
Correct answer: A) =IF(B2>=3000,"Y","N")
Explanation: Explanation: This formula checks if the value in B2 is greater than or equal to 3,000 and returns "Y" for true and "N" for false.
Question 35 of 50
A doctor wants to know how many patients have been diagnosed with a specific illness. Which function will filter the list of patients by diagnosis?
Correct answer: B) Filter
Explanation: Explanation: The Filter tool allows the doctor to display only the rows where a specific diagnosis is present.
Question 36 of 50
Which feature of Excel should be used to track the percentage of sales attributed to each product in a pie chart?
Correct answer: A) Data Labels
Explanation: Explanation: Data Labels show the exact values or percentages for each slice of a pie chart.
Question 37 of 50
A student wants to align all text to the left in an Excel worksheet. Which group on the Home Tab should they use?
Correct answer: C) Alignment
Explanation: Explanation: The Alignment group allows the user to control text alignment, including left-aligning the text in a cell.
Question 38 of 50
Which two options are available in Solver’s parameter dialog window for optimization analysis?
Correct answer: B) MIN and MAX
Explanation: Explanation: Solver allows users to set an objective to either maximize or minimize a particular value or to set it to a specific value.
Question 39 of 50
An Excel model includes formulas that reference other worksheets. What tool can be used to verify the cells referenced in these formulas?
Correct answer: B) Trace Precedents
Explanation: Explanation: The Trace Precedents tool shows which cells are referenced by a formula in the current worksheet.
Question 40 of 50
A table contains a list of sales for multiple products. Which formula will return the average sales for products that sold more than 100 units?
Correct answer: C) =AVERAGEIF()
Explanation: Explanation: The =AVERAGEIF() function calculates the average of values that meet a specified condition, such as sales greater than 100 units.
Question 41 of 50
Which function would calculate the maximum monthly sales for a range of data covering multiple months?
Correct answer: A) =MAX()
Explanation: Explanation: The =MAX() function returns the largest value in a specified range of data, such as monthly sales.
Question 42 of 50
Which feature in Excel allows you to add an additional filter to a data field in a PivotTable?
Correct answer: A) Slicer
Explanation: Explanation: A Slicer is a visual tool that provides an easy way to filter PivotTable data by different categories.
Question 43 of 50
Which formula will display "Good Job" if both the hours worked are less than the estimated hours and the quality is greater than 1?
Correct answer: B) =IF(AND(E16 1),"Good Job","")
Explanation: Explanation: The =IF() function checks if both conditions are met and returns "Good Job" if they are true.
Question 44 of 50
An analyst uses the CORREL function and gets a coefficient of 0.274 between two variables. What type of relationship does this indicate?
Correct answer: B) Weak correlation
Explanation: Explanation: A correlation coefficient of 0.274 indicates a weak relationship between the two variables.
Question 45 of 50
An employee earns an outcome bonus if the difficulty of the job is greater than 3 or the quality of their work equals 3. Which formula will calculate the outcome bonus eligibility?
Correct answer: C) =OR(G16>3,H16=3)
Explanation: Explanation: The =OR() function checks if either of the conditions (difficulty >3 or quality =3) is true and returns TRUE if either condition is met.
Question 46 of 50
A company tracks sales data for different departments. Which Excel feature should be used to create a report showing gross pay by department?
Correct answer: B) PivotTable
Explanation: Explanation: PivotTables are used to summarize large datasets, such as showing gross pay by department, in a concise report.
Question 47 of 50
Which feature in Excel allows you to visualize the differences in data between categories in a chart?
Correct answer: B) Legend
Explanation: Explanation: A Legend explains what each color or pattern represents in a chart, helping to differentiate between data categories.
Question 48 of 50
An owner wants to display the monthly sales tax based on a rate of 7.5% of total sales. Which formula will calculate the sales tax if monthly sales are in cells A1 to L1?
Correct answer: A) =SUM(A1:L1)*B2
Explanation: Explanation: This formula calculates the total sales tax by multiplying the total sales (A1:L1) by the sales tax rate in B2.
Question 49 of 50
A manager wants to calculate the percentage of total sales each product contributes to a company’s overall sales. Which chart type should be used?
Correct answer: A) Pie Chart
Explanation: Explanation: A Pie Chart is ideal for showing the proportion of total sales that each product contributes.
Question 50 of 50
Which formula will calculate how many employees are eligible for a time bonus, based on whether they worked fewer hours than estimated and their work quality exceeded 1?
Correct answer: D) =SUMIF(E16,C16,H16>1)
Explanation: Explanation: The =SUMIF() function checks for the conditions of fewer hours worked and quality greater than 1.
Set 2 complete
You scored 0%
0 correct out of 0 answered (50 questions in this set).