C268 Spreadsheets Practice Questions - Set 3 - Part 2

Test your knowledge of Spreadsheets concepts with these practice questions. Each question includes detailed explanations to help you understand the correct answers.

Question 21: A client wants to summarize sales data across multiple worksheets named by month abbreviations. Which formula will display the highest sales value for the year?

a) =MAX(JAN:DEC)

b) =MAX(JAN,DEC)

c) =SUM(JAN,FEB,MAR,APR)

d) =MAX(JAN,DEC,MAR)

Question 22: Which function will calculate the principal payment for a loan by subtracting the interest from the total payment?

a) =SUM(D20-E20)

b) =C20-D20

c) =PMT(C15-C12)

d) =SUM(C20-D20)

Question 23: A user needs to extract the first 5 characters from a text string in cell A1. Which formula should be used?

a) =RIGHT(A1,5)

b) =LEFT(A1,5)

c) =MID(A1,5)

d) =LEN(A1)

Question 24: Which Excel function allows users to sum values based on a condition, such as summing all sales greater than $500?

a) =COUNTIF()

b) =SUMIF()

c) =AVERAGEIF()

d) =IF()

Question 25: Which Excel feature allows users to filter data by adding a button to each column header that can be used to hide or show rows based on certain criteria?

a) Data Validation

b) Conditional Formatting

c) Filter

d) Slicer

Question 26: An employee wants to display all the data related to "Manufacturing" in a table with different industry types. What steps should they take?

a) Use Conditional Formatting to highlight "Manufacturing"

b) Click Filter and select "Manufacturing"

c) Use a PivotTable to summarize the data

d) Use Data Validation to restrict inputs to "Manufacturing"

Question 27: Which of the following features allows users to add additional filtering to a data field in a PivotTable?

a) Slicer

b) Data Validation

c) Conditional Formatting

d) Trace Precedents

Question 28: Which of the following chart types can be used to show the percentage breakdown of total sales for different products?

a) Pie Chart

b) Line Chart

c) Bar Chart

d) Column Chart

Question 29: A manager wants to know the total number of employees who have worked more than 40 hours in a week. Which formula should be used?

a) =COUNTIF(B1:B10,">40")

b) =SUMIF(B1:B10,">40")

c) =AVERAGEIF(B1:B10,">40")

d) =IF(B1>40)

Question 30: A PivotTable is used to calculate the total sales for each department. Which feature allows users to filter the data in the PivotTable based on department names?

a) Conditional Formatting

b) Slicer

c) Data Validation

d) Filter

Question 31: Which Excel formula will return "Passed" if a student scored more than 60 in cell B2 and "Failed" otherwise?

a) =IF(B2>60,"Passed","Failed")

b) =IF(B2<60,"Passed","Failed")

c) =IF(B2>60,FALSE,TRUE)

d) =B2>60

Question 32: An analyst wants to calculate the total revenue from products that sold more than 100 units. Which formula should they use?

a) =COUNTIF()

b) =SUMIF()

c) =AVERAGEIF()

d) =IF()

Question 33: A business owner tracks monthly sales in columns A through L. Which formula will return the total sales for the year?

a) =SUM(A1:L1)

b) =SUM(A1:A12)

c) =A1+A12

d) =AVERAGE(A1:L1)

Question 34: Which Excel feature helps create a concise report showing the total sales by product in a summarized format?

a) PivotTable

b) Filter

c) Conditional Formatting

d) Trace Precedents

Question 35: A client wants to calculate the present value of future payments of $500 each month for 2 years, at a 5% interest rate. Which function should they use?

a) =FV()

b) =NPER()

c) =PMT()

d) =PV()

Question 36: Which formula will return the number of unique inventory items in a list that have a quantity greater than zero?

a) =COUNTA(B1:B10)

b) =COUNTIF(B1:B10,">0")

c) =SUMIF(B1:B10,">0")

d) =IF(B1>0)

Question 37: A bank offers several different loan interest rates. Which tool should be used to create a scenario report showing the payments for each option?

a) Solver

b) Scenario Manager

c) Goal Seek

d) Data Validation

Question 38: A customer wants to calculate the maximum amount they can borrow with a $500 monthly payment for 2 years. Which Excel function should be used?

a) =NPER()

b) =RATE()

c) =PMT()

d) =PV()

Question 39: Which formula will count the number of cells in a range that contain a text string such as "Yes"?

a) =COUNTIF(A1:A10,"Yes")

b) =SUMIF(A1:A10,"Yes")

c) =IF(A1:A10,"Yes")

d) =AVERAGEIF(A1:A10,"Yes")

Question 40: A manager wants to calculate the total number of touch-ups performed on jobs with a difficulty rating of 3 or higher. Which function should be used?

a) =COUNTIF()

b) =SUMIF()

c) =IF()

d) =AVERAGEIF()

Don't Want to Study?

Save Time on Studies, Spend More with Family & Friends! Pay-After-you-Pass

Get Exam Support