Q: Suppose you want to classify orders as 'Large' if the quantity is at least 10,000 units. Which Excel formula would you use?

$$=IF(Quantity\u003e=10000, \text{\"Large\"}, \text{\"Small\"})$$

Question 1

Which of the following best describes the difference between a data set and a database?

Accepted Answer

A data set is a collection of data, while a database is a collection of related files containing records on people, places, or things.

Question 2

In Excel, what is the result of the formula $$=SUM(Table1[Savings])$$ when used in a table named Table1 with a column labeled 'Savings'?

Accepted Answer

It sums all values in the 'Savings' column, including any hidden rows.

Question 3

A company wants to identify the top 20% of products that account for 80% of its inventory value. Which analysis technique should they use?

Accepted Answer

Pareto analysis

Question 4

You want to filter a purchase order database in Excel to show only orders with an item cost of at least $200. Which sequence of actions is correct?

Accepted Answer

Click the filter arrow in the Item Cost column, select Number Filters, then choose Greater Than Or Equal To and enter 200.

Question 5

Which Excel function would you use to find the total cost of all orders that meet specific criteria in a database?

Accepted Answer

$$=DSUM(database, field, criteria)$$

Question 6

Suppose you want to classify orders as 'Large' if the quantity is at least 10,000 units. Which Excel formula would you use?

Accepted Answer

$$=IF(Quantity\u003e=10000, 	ext{"Large"}, 	ext{"Small"})$$

Question 7

A company offers quantity discounts: $$10 per unit for up to 1,000 units, 9 $$per unit for 1,001 to 5,000 units, and $7.50 per unit for more than 5,000 units. Which nested IF formula correctly calculates the total price for a given quantity Q?

Accepted Answer

$$=IF(Q\u003c=1000, Q*10, IF(Q\u003c=5000, Q*9, Q*7.5))$$

Question 8

Which of the following statements about the VLOOKUP function in Excel is TRUE?

Accepted Answer

If the range_lookup argument is FALSE, VLOOKUP will return #N/A if no exact match is found.

Question 9

You want to retrieve the sales value for a specific month and product from a table where months are in column A and products are in columns B to F. Which formula would you use?

Accepted Answer

$$=INDEX(A4:F15, MATCH(month, A4:A15, 0), MATCH(product, A3:F3, 0))$$

Question 10

Which of the following best describes the use of the Advanced Filter in Excel?

Accepted Answer

It allows you to define multiple criteria using 'and' and 'or' logic by specifying criteria in rows and columns.

Question 11

A PivotTable is created to summarize sales data by region and product. Which area should you drag the 'Region' field to in order to display regions as rows?

Accepted Answer

Rows area

Question 12

Which Excel function returns TRUE if a cell contains a number and FALSE otherwise?

Accepted Answer

$$=ISNUMBER(cell)$$

Question 13

You are designing a tax bracket calculator in Excel. Which combination of functions would best allow you to return the correct tax rate based on filing status and taxable income?

Accepted Answer

VLOOKUP and CHOOSE

Question 14

Which of the following is a key advantage of using Excel Tables for data analysis?

Accepted Answer

Formulas referencing table columns automatically update when new rows are added.

Question 15

A company wants to quickly summarize total revenue by region and product, and allow users to filter by payment type. Which Excel tool should they use?

Accepted Answer

PivotTable with a slicer for payment type

Question 16

Which of the following best describes the use of the MATCH function in Excel?

Accepted Answer

It returns the relative position of an item in a range that matches a specified value.

Question 17

You want to ensure that users can only enter whole numbers between 1 and 4 in a cell for filing status. Which Excel feature should you use?

Accepted Answer

Data Validation with whole number criteria

Question 18

Which of the following is TRUE about using the Filter tool in Excel?

Accepted Answer

Filtered records are hidden but not deleted, and calculations on the filtered range include hidden records unless copied elsewhere.

Database Analytics and Data Management for Business Statistics

Study Guide - Practice Questions

Study Guide - Flashcards